I've got my knickers in a twist with command bars

[AngAgius]

I have a file that creates to command bars Summary & Input. The Summary bar is only shown on the summary sheets and Input on some other sheets. I got that working fine.

The problem I have is the is an option for a user to save the file as an scenario and then recover the file. The save works fine. The user also has two identical files (apart from the data) for each category and could well be working on both at the same time.

On the recover the scenario is loaded and the original file closed. Closing the file deletes the menu bars. I have tried to make it "file" specific by using "thisWorkbook" in the code. Also if the user is working on both categories at once and closes one the Commandbars are deleted.

I tried not deleting the commandbars on close but then when I fired a macro it opened up the other file and fired the macro in there



Any help greatly appreciated

Sub CreateToolBarSummary()
Dim mybar As Object
Dim newmenu As Object
Dim Menu0 As Object
Dim Menu1 As Object
Dim Menu2 As Object
Dim Menu3 As Object
Dim MyBarName As String
Dim MySummary As String


On Error Resume Next
MySummary = ThisWorkbook.Name & "Sum"
Application.CommandBars(MySummary).Visible = True
If Err = 0 Then Exit Sub

MyBarName = ThisWorkbook.Name & "SummaryMenu"
mybar = MyBarName

Set mybar = CommandBars.Add(Name:=MySummary, Position:=msoBarTop, _
temporary:=True)
Set newmenu = Application.CommandBars(MySummary).Controls.Add(Type:=msoControlPopup, temporary:=True)
newmenu.Caption = " Click here for Refesh and Publish"

Set Menu0 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu0.Caption = "Refresh..."
Menu0.OnAction = "Refresh"
Menu0.FaceId = 459

Set Menu1 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu1.Caption = "Publish..."
Menu1.OnAction = "Publish"
Menu1.FaceId = 346

Set Menu2 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu2.Caption = "Show Detail..."
Menu2.OnAction = "GroupShowAllSummary"
Menu2.FaceId = 462

Set Menu3 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu3.Caption = "Hide Detail..."
Menu3.OnAction = "GroupHideAllSummary"
Menu3.FaceId = 464
End Sub




Sub CreateToolBarInput()
Dim mybar As Object
Dim newmenu As Object
Dim Menu0 As Object
Dim Menu1 As Object
Dim Menu2 As Object
Dim Menu3 As Object
Dim MyBarName As String
Dim MyInput As String


On Error Resume Next
MyInput = ThisWorkbook.Name & "Input"
Application.CommandBars(MyInput).Visible = True
If Err = 0 Then Exit Sub

MyBarName = ThisWorkbook.Name & "InputMenu"
mybar = MyBarName

Set mybar = CommandBars.Add(Name:=MyInput, Position:=msoBarTop, _
temporary:=True)
Set newmenu = Application.CommandBars(MyInput).Controls.Add(Type:=msoControlPopup, temporary:=True)
newmenu.Caption = " Click here for Options"

Set Menu0 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu0.Caption = "Create Senario..."
Menu0.OnAction = "senario_create"
Menu0.FaceId = 3

Set Menu1 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu1.Caption = "Recover Senario..."
Menu1.OnAction = "ReturnToPreviousSenario"
Menu1.FaceId = 23

Set Menu2 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu2.Caption = "Show Detail..."
Menu2.OnAction = "GroupShowAll"
Menu2.FaceId = 462

Set Menu1 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, Id:=1)
Menu1.Caption = "Hide Detail..."
Menu1.OnAction = "GroupHideAll"
Menu1.FaceId = 464
End Sub

Also in the workbook module

Private Sub Workbook_Open()
CreateToolBarSummary
CreateToolBarInput

Application.ScreenUpdating = False

Dim x As Integer

For x = Worksheets("Summary").Index To Worksheets("Other").Index
With Worksheets(x)
Worksheets(x).protect password:="****", UserInterfaceOnly:=True
End With
Next x

'to show correct menu
Worksheets("Other").Activate
Worksheets("Summary").Activate


Application.ScreenUpdating = True

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim MySummary As String
Dim MyInput As String

MySummary = ThisWorkbook.Name & "Sum"
MyInput = ThisWorkbook.Name & "Input"

Application.CommandBars(MyInput).Delete
Application.CommandBars(MySummary).Delete
End Sub

Private Sub Worksheet_Activate()

Dim MySummary As String
Dim MyInput As String

MySummary = ThisWorkbook.Name & "Sum"
MyInput = ThisWorkbook.Name & "Input"

'changes depending on sheet selected
Application.CommandBars(MyInput).Visible = False
Application.CommandBars(MySummary).Visible = True
End Sub

 

[AngAgius]

The recoved file does have the menubars but they are not visable

Edit:
However when I load to files together I get two lots of menu's

 

[AngAgius]

Ok I might be talking to myself but here goes.

I've simplified every thing by going with just One menu for all and all seems to work ok apart from....

When the senario is recoved I get a msg "Application-defined or object-difined error" but when I click OK every thing seems to work ok. Does anyone know what could be causing that?

 

[Damon Ostrander]

Hi AngAgius,

Regarding that error message, it would help (a lot) for us to know where in your code that error is occurring. If you click Debug when the message pops up the problem code should be highlighted.

Damon