Public variable is reset after addin macro completes - thread/focus pblm?

[hscowan]

Hello,

I have an addin macro (Excel 2K on Win XP) that creates a custo
worksheet and a custom toolbar for the sheet. The toolbar should onl
be active when the sheet is visible, and the user may add multipl
custom sheets as well as others.

The problem is that the public variable (SababarIsActive) I am using t
track if the toolbar is active is reset to false once the macr
completes.


The public variable "SababarIsActive" is added to a standard module an
the following event handler is added to the "thisWorkbook" module b
the addin macro:


Code
-------------------


------------------- Saba_GlobalVarDefs module ---------------
Option Explicit

Public SababarIsActive As Boolean, SababarExists As Boolean 'SabaFunctiona

------------------- thisWorkbook module -----------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'SabaFunctionb

Set_Security_Level_Proc_Run

MsgBox (\"Hello from the Workbook_SheetActivate procedure...\")
DisplayVal \"SababarIsActive 1\", SababarIsActive
If Range(\"A1\").Value = \"Space Air Balance Analysis\" Then
MsgBox (\"Range-A1.Value = Space Air Balance Analysis...\")
If Application.CommandBars(\"Sababar\").Enabled = False Or _
Not SababarIsActive Then
MsgBox (\" logic for: Not SababarIsActive ... togle toolbars\")
Toggle_CommandBars
SababarIsActive = True
End If

Set_Security_Level_User
DisplayVal \"SababarIsActive 2\", SababarIsActive
Exit Sub
End If

' deactivate custom controls
If SababarIsActive Then
Toggle_CommandBars
SababarIsActive = False
End If

DisplayVal \"SababarIsActive 3\", SababarIsActive
Set_Security_Level_Off

End Sub 'SabaFunctionc



-------------------


In the last lines of the macro, I'm using

Code
-------------------


Worksheets(2).Activate
Worksheets(1).Activate


-------------------

the "Workbook_SheetActivate" code is triggered correctly and message
are in sequence:

Worksheets(2).Activate:
1) Hello from the Workbook_SheetActivate procedure...
2) SababarIsActive 1 = false
3) SababarIsActive 3 = false

Worksheets(1).Activate:
1) Hello from the Workbook_SheetActivate procedure...
2) SababarIsActive 1 = false
3) "Range-A1.Value = Space Air Balance Analysis...
4) logic for: Not SababarIsActive ... togle toolbars
5) *SababarIsActive 1 = true*

However, once the macro completes, and the "Workbook_SheetActivate
code is triggered by manually clicking on a different sheet
*"SababarIsActive " is False!* And of course it now retains the valu
each time it is flipped, unfortunatly it is logically incorrec
(inverted).


I think the problem is that the addin macro is in a workbook differen
from the event handler and this is causing the problem....

Is this a threading problem or some kind of focus/visibility issue?

Does anybody know how to get this so that "SababarIsActive " wil
retain the correct value?

thank you,
HSC

I had posted similar to this in another forum but had no resolution
please excuse the apparent repetition..

 

[Bernie Deitrick]

HSC,

All variables get set to nothing when code finishes. If you want to have
the value be non-volatile, you will need to read and write the value to
something that is non-volatile, for example a cell, or a named range, or a
custom workbook property, or the registry. Post back if you need help with
whichever option you choose.

Another option is to simply make the toolbar active when your sheet is
activated, and deactivate it when the sheet is deactivated. You can use the
workbook's worksheet activate and deactivate events to do that.

HTH,
Bernie
MS Excel MVP