Public variables

[Michael Simpson]

First of all, my apologies for my question. I know that if I google public + variables + vba I will get 1000's of answers, but based on the way my code "doesn't" work, I'm misunderstanding it.

I am an experienced programmer in other languages, but self-taught in VBA. One of the comments I often see about public variables is, basically, try and avoid using them. Let me explain first why I think I need to use them in this particular case.

I have a Worksheet_SelectionChange event for sheet 1. I can start a data update userform (modeless) and the idea is that, when the user starts this userform via a ribbon-defined program, the code will automatically fill in the controls on the form based on the current row selected on sheet 1. Since it's modeless, they can then select any other row from sheet 1, and its values will be "transferred" and shown on the user form.

I have also defined another userform that allows them to select (via a listbox) which rows from sheet 1 should be included in a word document. For each item in the listbox they select, I then toggle the colouring for the relevant row in sheet 1.

Here's the problem. If they're running the listbox userform, I want to toggle the colouring for the selected row. If they're running the data update userform, I only want to transfer the contents of the row to the userform. I don't want to toggle the possible colouring of the row.

Okay. Logical solution (?) would be to have a global variable indicating whether I'm running the data update form or not, that way, when the Worksheet_SelectionChange kicks in, I can tell whether I'm running the data update form or not.

To my problem (and original question).

I understand (?) that a public variable has to be placed in a module, but what isn't clear to me is whether any/all public variables in that module only are defined when any procedure in the module is run or whether they are "extracted" automatically and defined when the workbook is activated. If the former, then, again logically to me, they definitions should be placed in This Workbook with the initial setting of the variable in (say) Workbook_Activate.

What am I not understanding here ?

 

[Michael Simpson]

Thanks Rory. What's probably happening is that I'm hitting an error in the code and choosing End rather than Debug - would that be what you mean ?

(Hmmmm -end in the code. What does that do. Neither of my "teach yourself VBA" include an explanation)

 

[RoryA]

Yes, that would do it. End in code has the same effect.

 

[Norie]

Michael

Why not allow the user to select a row/rows using the userform?

 

[Michael Simpson]

Possibly could do Norie, but that would require quite some reworking of the code and design is too far down the road. (I have a suspicion that , in reality, when they run the userform, they'll simply close it, select the required row and then restart the form again).