Keeping ComboBox Lists Populated

Question

I have created a Word 2010 VBA Macro Sub with a UserForm. The Sub searches for ==Codes== in a form document, places the found ==code== as a label into the Userform and then allows the user to replace the ==code== with his or her input in the Combobox (part of the same UserForm).

Each string of inputted data is then saved to the Combobox list in the UserForm for later selection if needed.

This works fine until this Macro/Userform expires because a searched document is completed (or cancelled).

I would then like to open the next form document, and in the new launch of this same Macro/Sub retain the former combobox list of data (as options to fill this next opened document - for instance, the code ==Client Name== will come up frequently, and I'd rather select a combobox list entry rather than having to type the client name over and over)

But I can't seem to keep the combobox list in the new launch of this Macro Sub populated with the previous combobox data - even if I isolate this routine as a separate module and pre-define the variables with "Public" dimensions.

So, before I knock myself out trying to figure this out ... just a simple question:

Once a Macro terminates are all of the Public variables "dropped"? When I used to program in DOS WP.51 Macros you could keep data strings in the RAM endlessly (until you "killed" them, or closed WP)

If the Public variable are not "dropped", could someone give me a sample of code by which Public variables could be retained and populated into a duplicately launched combobox userform.

Any ideas, howsoever brief, would help

Thanks much in advance. . .

Mike

Just to get the idea correct. I assume you have this UserForm Macro stored into the Normal Template? and then when you open the "next form document" you would have closed Word and then opened it again? — Jean-Pierre Oosthuizen

OpiesDad OpiesDad · Accepted Answer · 2016-01-19T00:31:08

Not entirely sure what you're trying to do, but what I'd recommend is the following (Assuming that the form is named, "UserForm1" and then "UserForm2":

1) Create a Module that you use to open the form using:

Sub test()
    UserForm1.Show
    'Rest of things that you want to do...you will be able to access the values in your combobox in userform1
    UserForm2.Show
End Sub

2) In your UserForm1, include a button that will close the form and include the following code:

Sub btn_Exit_Click()
    Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = 0 Then
        Cancel = True
        MsgBox "The X is disabled, please use a button on the form.", vbCritical
    End If

End Sub

This will allow you to maintain control of the UserForm and ensure that you can keep the values. It may be possible to disable the Closing button, but I wasn't able to figure it out (my experience with forms is mostly in Access and those have different properties).

Hiding the form keeps the values so that you can look at them whereas when the user closes the form you lose the values that were in it.

*Note: Code to disable the X button taken from VBA Express

Keeping ComboBox Lists Populated

1 Answers