publically defined command bar variable problem

[Guest]

I have a publically defined command bar variable in the ThisWorkbook module and I am calling its Visible method from a Worksheet Module. The Worksheet can't call the method of publically defined command bar variable. The idea is that I only want the command bars to show when the user is in the Worksheet and to disappear when he/she is not. "SizeSystem" and "CreateReport" are two publically defiend Subs in another module.

Here's the code:
Workbook:
Option Explicit

Public oCB As Office.CommandBar
Public oCBP As Office.CommandBarPopup

Private Const MAIN_MENU As String = "Worksheet Menu Bar"
Private Const TOOLS_MENU As String = "Tools"
Private Const AUTO_SIZE As String = "Auto Size System"
Private Const CREATE_REPORT As String = "Create Summary Report"
Private Const CMD_TAG As String = "Vericis"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call destroyMenuItems
End Sub

Private Sub Workbook_Open()
Call createMenuItems
End Sub

Private Sub createMenuItems()
Dim oCBB As Office.CommandBarButton
Dim oCBB2 As Office.CommandBarButton

Set oCBP = application.CommandBars(MAIN_MENU).Controls(TOOLS_MENU).Controls.Add(Type:=msoControlPopup, Temporary:=True)
With oCBP
.Caption = "Vericis Questionnaire"
.BeginGroup = True
.Tag = CMD_TAG

Set oCBB = .Controls.Add(msoControlButton)
With oCBB
.Caption = AUTO_SIZE
.FaceId = 123
.Tag = AUTO_SIZE
.OnAction = "SizeSystem"
End With

Set oCBB2 = .Controls.Add(msoControlButton)
With oCBB2
.Caption = CREATE_REPORT
.FaceId = 127
.BeginGroup = True
.Tag = CREATE_REPORT
.OnAction = "CreateReport"
End With
End With

Set oCB = application.CommandBars.Add(Name:=CMD_TAG, Temporary:=True)
With oCB
.Left = 600
.Top = 300

Set oCBB = .Controls.Add(Type:=msoControlButton)
With oCBB
.FaceId = 123
.ToolTipText = AUTO_SIZE
.Style = msoButtonIcon
.Tag = AUTO_SIZE
.OnAction = "SizeSystem"
End With

Set oCBB2 = .Controls.Add(Type:=msoControlButton)
With oCBB2
.FaceId = 127
.ToolTipText = CREATE_REPORT
.Style = msoButtonIcon
.Tag = CREATE_REPORT
.OnAction = "CreateReport"
End With
End With
End Sub

Private Sub destroyMenuItems()
With application
.CommandBars(MAIN_MENU).FindControl(Tag:=CMD_TAG, Recursive:=True).Delete
.CommandBars(CMD_TAG).Delete
End With
End Sub



Worksheet:

Option Explicit

Private Sub Worksheet_Activate()
oCB.Visible = True
oCBP.Visible = True
End Sub

Private Sub Worksheet_Deactivate()
oCB.Visible = False
oCBP.Visible = False
End Sub

Does anyone have any ideas as to why this is?

Thanks,

Joe

 

[Bob Phillips]

Joe,

ThisWorkbook is a class module, as are the Worksheet modules, so you need to
qualify with the class object.

Try this

Worksheet:

Option Explicit

Private Sub Worksheet_Activate()
ThisWorkbook.oCB.Visible = True
ThisWorkbook.oCBP.Visible = True
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.oCB.Visible = False
ThisWorkbook.oCBP.Visible = False
End Sub


--

HTH

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

I have a publically defined command bar variable in the ThisWorkbook

module and I am calling its Visible method from a Worksheet Module. The
Worksheet can't call the method of publically defined command bar variable.
The idea is that I only want the command bars to show when the user is in
the Worksheet and to disappear when he/she is not. "SizeSystem" and
"CreateReport" are two publically defiend Subs in another module.

Here's the code:
Workbook:
Option Explicit

Public oCB As Office.CommandBar
Public oCBP As Office.CommandBarPopup

Private Const MAIN_MENU As String = "Worksheet Menu Bar"
Private Const TOOLS_MENU As String = "Tools"
Private Const AUTO_SIZE As String = "Auto Size System"
Private Const CREATE_REPORT As String = "Create Summary Report"
Private Const CMD_TAG As String = "Vericis"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call destroyMenuItems
End Sub

Private Sub Workbook_Open()
Call createMenuItems
End Sub

Private Sub createMenuItems()
Dim oCBB As Office.CommandBarButton
Dim oCBB2 As Office.CommandBarButton

Set oCBP =

application.CommandBars(MAIN_MENU).Controls(TOOLS_MENU).Controls.Add(Type:=m
soControlPopup, Temporary:=True)