How can I save variable when 'end'? The user then reactivates the

[OlieH]

I auto_open a macro, set variables then exit the mcro to allow users to
directly enter values in the worksheet. When they are through they press a
button that reactivates the aarco. My problem is the variablest that I have
set are lost. Is there a way to save these variables. I currently save then
in portion of a work sheet the user does not see. I would like a better way.
thanks for the help
OlieH

 

[Billy Liddel]

Olie

I always save to a hidden sheet, it seems the easiest way. I you do not want
the user to see the sheet you can set it to Very Hidden with VB so the user
wil not see the sheet in the list of hidden sheets. You will have to unhide
the sheet again using a macro.

Regards
Peter Atherton

 

[OlieH]

Thanks for the reply Peter/Billy.

Olie

I always save to a hidden sheet, it seems the easiest way. I you do not want
the user to see the sheet you can set it to Very Hidden with VB so the user
wil not see the sheet in the list of hidden sheets. You will have to unhide
the sheet again using a macro.

Regards
Peter Atherton

 

[Dave Peterson]

I think you should rewrite your code so that you don't use that End statement
(not end with, not end if--just the plain old End).

Then your static variables will still hold their values.

On the other hand, lots of things can go wrong. I would include a routine that
would repopulate those variables.



Add one more public/static variable:

Public VariablesAreInitialized as boolean

Then later you can use:

if variablesareinitialized then
'keep going
else
call dedicatedroutinetoinitializevariables 'not rely on Auto_open
end if

sub dedicatedroutinetoinitializevariables()
set TWB = somethingoranother
...all you need
variablesareinitialized = true
end sub

just in case something unexpected goes wrong.

(or
if twb is nothing then
call dedicatedroutinetoinitializevariables
end if