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
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