Global Variable in Excel 1

[grnzbra]

I have an Excel .xlsm file that has several worksheets. The first worksheet has code in the deactivate event which validates data entered. When one tries to go to any of the other worksheets, if an error is found on the first worksheet, the first sheet is activated and the offending cell is highlighted. In the activate event for the other sheets, there is code that should not run if there is an error on the first sheet.

There is a module called ThisWorkbook which seems to be able to be seen by all the other worksheets. In this I created a flag, NoSort, which is set when an error is discovered in the first sheet. This module also has other code which runs whenever the first worksheet is deactivated.

The problem is this. When an error is found, the NoSort flag is set and when the other code in the ThisWorksheet module is run, the value entered in the NoSort flag is correct right up to the End Sub breakpoint. When I check NoSort, the value is correct. When I step, the focus goes to the activate event of the second worksheet and the NoSort flag is reset.

How do I create a truly global variable which will hold whatever value has been set, no matter what code is run, as long as it is not code that says NoSort = True or NoSort = False?

 

[SkipVought]

Hi,

HOW and WHERE have you declared your NoSort variable?

This will determine the Type and Scope of your variable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Both ThisWorkbook and worksheet modules are class modules associated with excel objects (workbook, worksheets). They extend standard objects and can be used to handle object's events and keep extra object data.
Even if you declare public variable in class module, it can be accessed only via the object, so for your purpose a better way is to add regular module and declare variable there.

combo

 

[grnzbra]

Thanks, combo. That did the trick.

Skip,

Thanks also.That was kinda the question I was trying to ask. I'll have to make a note to be sure that what I am asking is clear.​

 

[Andrzejek]

grnzbra,
You may want to ready about Scope of variables in VBA

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[grnzbra]

Andrzejek,

Thank you for the suggestion. Will check it out.