Global Variable on Workbook_Open

Hi,

I am not sure that what I am trying to do is possible but this is what I need (and not able to achieve). I do have an excel file with different Sheets and, on activating a sheet a message appear.

As example this is the code on Sheet6:


Private Sub Worksheet_Activate()

MsgBox "You shouldn't be here."

End Sub


I would like to have a YES/NO message in ThisWorkbook with Workbook_Open() where an user will receive a question like "do you want to display guide" Y/N.

If Y is selected the message will be displayed if N is selected then the messages won't be displayed. So I do need a global variable where to store the answer to the question on opening the file and, with an if function, on activating the other Sheets the message will be displayed or not.

So, as example, the previous code on Sheet6 would be:


Private Sub Worksheet_Activate() 'not sure at this point is it is still correct to use the Private Sub definition

if answer=YES then

MsgBox "You shouldn't be here."

endif

End Sub


I tried different ways but I am not too confident with global variable and, after reading a lot in different forums, at this point I am not even sure that you can define a global variable in the ThisWorkbook Sheet and neither if global variable are working within Sheets Sub (on some sites they way saying, if I understood correctly, that global variable can be defined and used only within module.

Any idea on how to make the above working, if possible?

Thank you for the help.

Hi there,

I am not even sure that you can define a global variable in the ThisWorkbook Sheet

After some very quick experimentation, it seems that interestingly (strangely!) you can declare a public variable in the ThisWorkbook VBA CodeModule, and the compiler will not generate an error message. HOWEVER, the variable will NOT function as a public variable - i.e. a "Variable not defined" error message will be generated if code in another module refers to this so-called "public" variable.


In general, public variables should be initialised each time a top-level (entry-point) routine is executed, and it is not really good practice to assume that such variables will retain their values between calls to entry-point routines - situations such as code crashes etc. can cause public variables to be reset to their default values.

In your case it might be better to store the User's response to the initial "Do you want to display guide?" question as the value of a workbook-level Defined Name, and to use that value when determining whether or not to display other messages to that User.


Hope this helps.

Regards,

Greg M

Hi Greg,

Thank you very much for your response, that was helpful and actually was exactly what I was experiencing (was able to define a global variable in the ThisWorkbook VBA CodeModule but then receiving a compiling error when adding this variable in other Sheets code.

Unfortunately my VBA knowledge are somehow limited so I am not too sure what is a workbook-level Defined Name (and google didn't help).

Can you help with a code example?

Basically this is what I would like to have in ThisWorkboos:


Private Sub Workbook_Open()

answer = MsgBox("Text", vbQuestion + vbYesNo + vbDefaultButton2, "Message Box Title")

End Sub


And below what I would like to have in Sheet6:


Private Sub Worksheet_Activate()

if answer = vbYes then

MsgBox "You shouldn't be here, only Max or Greg should touch this Sheet."

endif

End Sub


Can you give me, if you can, some tips on how and where to define the variable answer to make this work and if I should change the Private (in the sub definition) to Public or remove it?

Thank you very much for your help and time.

Thank you for your answer.

So do I define the variable as normal and then use it as you mentioned? I tried this but on going to Sheet6 is giving me a compile error as Method or data member not found.

This the code in ThisWorkBook:



Private Sub Workbook_Open()

Dim answer as Integer

answer = MsgBox("Do you want the guide", vbQuestion + vbYesNo + vbDefaultButton2, "Select Yes or No")

End Sub



And below what I would like to have in Sheet6:


Private Sub Worksheet_Activate()

if ThisWorkbook.answer = vbYes then

MsgBox "You shouldn't be here."

endif

End Sub



Maybe I have to declare answer as Global before the Private Sub Workbook_Open()?

It worked like a charm.

Thank you very much.

Hi rorya,

ThisWorkbook is a class, so any public variable declared there is a member of that class, and only accessible as such

Thanks for the memory refresher!

I always use class properties rather than class public variables, so I had forgotten that public variables can in fact be declared in class modules.

Regards,

Greg M