Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
publically defined command bar variable problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
publically defined command bar variable problem
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) "Joe" wrote in message ... 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) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create defined name using variable file name in reference | Excel Worksheet Functions | |||
Variable sum from defined input | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Variable Not Defined? objPivotCache | Excel Programming | |||
How to copy worksheets with a user defined variable | Excel Programming |