Variable lifetime across macros
Posted by Eric Anderson on December 18, 2001 12:10 PM
I have a userform that is shown when a cell selection change is made (Worksheet_SelectionChange). I want to save the settings that the user makes on the userform so that every time the form appears it will have the same settings as when previously shown.
I have tried using Public variables within a standard module to store the settings, but apparently Excel treats each showing of the form as a separate run and therefore reinitializes all variables.
Do you have any suggestions?
Thanks
Posted by Juan Pablo G. on December 18, 2001 12:13 PM
I think the problem is that you are closing the user form with this line:
Unload Me
Or something similar. This will KILL the user form, and therefor, all variables contained in it. To avoid this, only Hide it, with
Me.Hide
You should be set.
Juan Pablo G.
Posted by Eric Anderson on December 18, 2001 12:17 PM
Thanks for quick reply. I AM using Me.Hide and still having the problem.
standard module to store the settings, but apparently Excel treats each showing of the form as a separate run and therefore reinitializes all variables.
Posted by Juan Pablo G. on December 18, 2001 12:23 PM
Really ? Where did you put the Public Variables ? they should be in a standard module, not in the UserForm's.
Juan Pablo G. for quick reply. I AM using Me.Hide and still having the problem.
Posted by Rick E on December 18, 2001 12:50 PM
Well things don't aways work the way they should, what I would do, is put each one of the objects "values" in a cell on a worksheet that is not used or in a place not seen, as in AB3000 etc.
So as the form is loaded, take the values off the worksheet, (blanks or empty the first time) and write them to those cells as they are changed.
At least that is a work around to this problem.
Posted by Eric Anderson on December 18, 2001 1:36 PM
If you supply the exact formula and the cell it's in (important for my solution) I can give you a result
Posted by Mark O'Brien on December 18, 2001 2:27 PM
I used to have hellatious problems with scope of variables when using userforms. I think I got round the problem by using Class Modules as containers for the data. Really ? Where did you put the Public Variables ? they should be in a standard module, not in the UserForm's. for quick reply. I AM using Me.Hide and still having the problem. :
