Public variable does not retain value

  • Thread starter Thread starter hscowan
  • Start date Start date
H

hscowan

Hi Folks,

I have a VBA module that:
1) creates a custom spreadsheet
2) creates a temporary command bar
3) adds vba code to the workbook module for specific events
4) controls toolbars per work sheet using event handlers


When the new custom sheet is created a toolbar is created (if not
already in existence). Near the end of the main module, to activate the
toolbar, I am using

Code:
--------------------

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

thus triggering the "Workbook_SheetActivate" code (which is
created/added by the mainsubroutine ).

The "thisWorkbook" module contains:

Code:
--------------------
Public SababarIsActive, SababarExists As Boolean

... other events coded for here ...

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'SabaFunctionb

Set_Security_Level_Proc_Run

MsgBox ("this is the Workbook_SheetActivate event handler.")

If Range("A1").Value = "Space Air Balance Analysis" Then
If Application.CommandBars("Sababar").Enabled = False Or _
Not SababarIsActive Then
Toggle_CommandBars
*SababarIsActive = True*
End If

Set_Security_Level_User
Exit Sub
End If

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

Set_Security_Level_Off

End Sub


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


I have a pop-up to see that this module is executing upon creation, and
it does. The sheets are switched and the correct toolbar is displayed.

The problem is that the public variables SababarIsActive and
SababarExists do not retain thier values once the main subroutine
completes execution. So, when I change sheets again, the macro has the
opposite value and causes the wrong toolbar to be displayed.

I thought that by setting the value in the Workbook_SheetActivate
subroutine it would be retained throughout, but it seems to be lost
when the main macro completes.

Any help appreciated.

thanks in advance,
Scott
 
Scott,

Move the public variables from the ThisWorkbook module to a standard code
module, you will find that they retain okay then.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,

thanks for the tip (I moved them to a standard module.), but I am stil
encountering the same problems.
I think the problem is that when I run my main macro, it has contro
over the memory pool or a threading problem. Perhaps I should hav
specified that the "main macro" is in an Add-In, and th
"Workbook_SheetActivate" procedure is created in a new workbook.

When I run it now, the toolbar is correctly displayed, but the value o
"SababarIsActive" is false once the macro completes. So, this causes a
inverted status condition.

I tried to only activate sheet(2) programatically, and this seems t
allow the public variable to be correctly set to true when
"physically" activate the custom sheet by clicking the name with th
mouse - this is of course after the main macro has done all it's wor
and only the event handlers are activated.

Is there a way to simulate this?
Is this a scenario requiring another thread to run after the main macr
has completed?


thanks,
Scot
 
Back
Top