1

Please excuse any descriptive errors I am new user of VBA. I have two userforms. The first has several frames with seperate text-box and command buttons. One such frame has a command button that opens another userform with further text-boxes and a command button (““Enter””). According to all forum articles I have read, if I declare the variables I desire to be transferred from the second userform back to the first as ““Public”” and place them in a ““Normal”” module the values should be retained. I believe I have declared these variables in this way correctly and yet the values are not being retained. What is the most likely cause of the problem? Thanks

2

Try to not close the form you want to get the values from. Once the parent form is closed, the memory of it is also gone. The best way I have found (MS Access) is to Hide the parent form after saving it.
This will enable you to refer to another object (the parent form) with out viewing it assuming you don’t want a user to see it. Hope that helps

3

Hi Sudhir yes I tried that and it didn’t work unfortunately. However, I developed a workaround in which I generated a completely new set of public variables and set those equal to the control variables and this seems to be working fine. The only downside is that I wonder if I am using more variables than I really need. I gues being inexperienced I am not aware if this will be excessively punitive in the long run. We’ll have to see. Thanks again to those that replied.

4

Try with giving form reference before variable e.g.

in userform1 variable is Myvar then type in userform 2 as userform1.Myvar

Sudhir Gawade.

5

Leo,

My understanding is that Public Variables should not be placed in an Object (Userform) module to have a truly Global scope. In fact, I have already tried that and I get a compiler error.

Terry

6

Michael,

Thanks for that. Well I am using a button_click event to Enter the data. In that button_click event for userform2, I have tried both setting the control variables from userform1 equal to control variables from userform2, and also within a button_click event of userform1 setting the controls from userform1 equal to the control variables from userform2. Neither seem to work.

When you say assignment statements do you mean anything more than control1userform1 = control2userform2 type statement or something else?

Terry

7

Terry,

I wonder how you are linking the public variables in the module to the controls on the userform.

The best way to ensure that the variables get set to the values of the controls is to create an event procedure that is called whenever the form closes (this can be a Form_Close event, or a Button_Click event). In the event procedure, use a series of assignment statements to copy the value of each control to the corresponding variable.

Michael S. Meyers-Jouan

----- Reply message -----

8

Terry,

I wonder how you are linking the public variables in the module to the controls on the userform.

The best way to ensure that the variables get set to the values of the controls is to create an event procedure that is called whenever the form closes (this can be a Form_Close event, or a Button_Click event). In the event procedure, use a series of assignment statements to copy the value of each control to the corresponding variable.

Michael S. Meyers-Jouan
From my phone

----- Reply message -----

9

Terry,

I wonder how you are linking the public variables in the module to the controls on the userform.

The best way to ensure that the variables get set to the values of the controls is to create an event procedure that is called whenever the form closes (this can be a Form_Close event, or a Button_Click event). In the event procedure, use a series of assignment statements to copy the value of each control to the corresponding variable.

Michael S. Meyers-Jouan

10

Try creating public variables or properties on the form you want to transfer the values to and then on the secondary form(s) set the public values on the public variables on the first form as they become available on the second form.

11

Dear Michael, Thanks for taking the time to write. Hopefully, we’ll be able to figure this out quite quickly. The answer to your question above is: I am using userforms with Excel 2007 and I have a couple of independant modules which contain Public/Global variables which are also textbox titles (although other variables I am using within userform 2 also don’t transfer back to userform1). I am not sure if this is correct, but I have tried making them Public variables, and “not”, and it doesnt seem to make any difference to the outcome. In order to help you know what I know and don’t know (which is quite a bit), I am aware, of what hide/unhide is and also load/unload although I don’t claim to know exactly if either of these has an impact on the resulting loss of the values. I have tried a number of different combinations and nothing seems to preserve the values of these variables. Note also that I am calling the second userform from a Public sub
using .load then unload…within the second userform I am using “hide” after the textboxes have been completed…I have tried “passing” these variables as well but that doesnt seem to work…what next? Thanks again…Terry

12

Visual Basic for Applications (VBA) is a version of Visual Basic that is used as a programming tool within specific applications. Can you tell us which application you are writing your VBA code in?

The reason I ask is that you use the term “userform”, which I associate with the very limited “forms” available in Microsoft Excel. The forms available in other applications such as Microsoft Access and Microsoft Word are a different matter.

If you are working in Microsoft Excel, it is important to note that there are two different kinds of modules available: each worksheet has a code module associated with it, but there is also an independent module that you can create using the Insert Module menu command (for the moment, we’ll ignore the third kind of module, because Class Modules add nothing to this discussion).

A variable in a worksheet’s code module is primarily accessible only to code in that module; on the other hand, public variables in an independent module are truly global, and should preserve values when procedures that have set their values are no longer available (as, for example, when the procedures are defined in a form that is no longer open).

Michael S. Meyers-Jouan