Global variables that last..

[Fherrera]

Hi, I think i'm a little lost. I was hoping to be able to save information once the Excel sheet is open...

I have a userform that display's a set of default values in the text boxes and list boxes. I'd like to be able to save the previously selected choices so that they appear instead of the default values when the userform is opened again. I prefer it went back to normal and used the default values when the excel sheet is closed.

My reasoning: create a global variable that gets updated to some specific value in the open workbook event. Then, compare these "default" values when the userform is initializing. The only thing I didn't realize at the time (it's a long weekend up here in Canada and the fireworks show kept me up last night thus i'm running on really low batteries) was that if i initialize the global variables when I open the workbook. The code stops executing. The userform eventually gets called, but the global variables I had initialized before are no longer initialized. I only want to initialize them once.

My options: create yet another spreadsheet to hold these temporary values or create a text/cfg file somewhere that does the same thing.

Does anyone know any other options or how this 'problem' could be better solved?

Thanks

Frank

 

[SkipVought]

Frank,

Yes. Create a sheet that the Visible property is is xlSheetVeryHidden when you save the workbook. Make the property xlSheetVisible when you are in Maintenance Mode, xlSheetVeryHidden otherwise -- a Mode that you define.

You can keep a bunch of stuff on this sheet that can be more persistant than a Public variable.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[VBAjedi]

I personally prefer the route of adding a spreadsheet to hold those values. Put them in named ranges for easy reference from the code, and make the sheet xlveryhidden after you are done developing the tool (so the users never even know the sheet is there).

VBAjedi