Custom Menu not opening when called from another sheet

[Absolution]

I have a set of *workbooks* which I access from a *workbook laid out like a menu* with form buttons assigned to macros. The *workbook* I am calling from has the following macro:

Sub OpenArtefact()
'
' OpenArtefact Macro

    Workbooks.Open Filename:= _
        "C:\SerimRal\Game 23 Information\Artefact Rumours.xls"

End Sub

The *workbook* I am calling from this has the following code:

Public Const TbarName = "SR23 Profile Options"

Sub Auto_Open()

On Error GoTo Errorhandler

Dim c
Dim i As Integer
Dim customBar As CommandBar
Dim CBar1

Dim newButton As CommandBarButton

'************************************************************************************
' Check the existing toolbars - if the one you want exists, make it visible
'************************************************************************************

For Each c In CommandBars
    If c.Name = TbarName Then
        c.Visible = True
        Application.CommandBars(TbarName).Controls(1).Enabled = True
        Application.CommandBars(TbarName).Controls(4).Enabled = True
        For i = 2 To 3
            Application.CommandBars(TbarName).Controls(i).Enabled = True
        Next i
        c.Delete
        Exit Sub
    End If
Next

'*************************************************************************************
' If haven't exited add the toolbar
' Position determines where it's displayed (e.g. msoBarTop, ...left or floating)
'*************************************************************************************
Set CBar1 = CommandBars.Add(Name:=TbarName, Position:=msoBarFloating, Temporary:=False)


With Application
    
    
    '*********************************************************************************
    ' Add the number of controls that you want
    '*********************************************************************************
    For i = 1 To 7
        CBar1.Controls.Add
    Next i
    
    '*********************************************************************************
    ' Assign the properties for each control
    '
    '*********************************************************************************
    With CBar1
        .Controls(1).Style = msoButtonCaption
        .Controls(1).OnAction = "FindArtefact"
        .Controls(1).Caption = "Locate Artefact    "
        .Controls(1).TooltipText = "Locate Artefact"
        .Controls(1).Enabled = True
        
        

        .Controls(2).Style = msoButtonCaption
        .Controls(2).OnAction = "ManualArtInputter"
        .Controls(2).Caption = "Manual Input        "
        .Controls(2).TooltipText = "Clear Fields to enable Location from manual Source"
        .Controls(2).Enabled = True
        
        
        .Controls(3).Style = msoButtonCaption
        .Controls(3).OnAction = "AutoArtefactInput"
        .Controls(3).Caption = "Auto Input            "
        .Controls(3).TooltipText = "Allows Location from listed rumours"
        .Controls(3).Enabled = True
        
        .Controls(4).Style = msoButtonCaption
        .Controls(4).OnAction = "GotoFreeform"
        .Controls(4).Caption = "Enter Freeform Results"
        .Controls(4).TooltipText = "Allows Input of realm or know areas"
        .Controls(4).Enabled = True
        
        .Controls(5).Style = msoButtonCaption
        .Controls(5).OnAction = "GotoCastle"
        .Controls(5).Caption = "Go To Castle List     "
        .Controls(5).TooltipText = "Moves to castle listings"
        .Controls(5).Enabled = True
        
        .Controls(6).Style = msoButtonCaption
        .Controls(6).OnAction = "GotoArtifacts"
        .Controls(6).Caption = "Go to Artefact Rumours"
        .Controls(6).TooltipText = "Moves to Artefact Rumour Sheet"
        .Controls(6).Enabled = True
        
        .Controls(7).Style = msoButtonCaption
        .Controls(7).OnAction = "ExitProfile"
        .Controls(7).Caption = "Exit Sheet              "
        .Controls(7).TooltipText = "Exits the Sheet"
        .Controls(7).Enabled = True
        
        
        '*********************************************************************************
        ' The "pop up" menu is a control with controls added to its commandbar
        '*********************************************************************************
        Application.CommandBars(TbarName).Controls.Add Type:=msoControlPopup, Before:=4
        .Controls(4).TooltipText = "Update Option"
        .Controls(4).Caption = "Updates                   "
        
        With Application.CommandBars(TbarName).Controls(4).CommandBar
            For i = 1 To 5
                .Controls.Add
            Next
            .Controls(1).Style = msoButtonCaption
            .Controls(1).OnAction = "UpdateGodNames"
            .Controls(1).Caption = "Update God Names"
            .Controls(1).TooltipText = "Updates Sheet to add new god names"
       
            .Controls(2).Style = msoButtonCaption
            .Controls(2).OnAction = "SortRumours"
            .Controls(2).Caption = "Sort Rumours"
            .Controls(2).TooltipText = "Sorts the Artefacts Rumours"
            
            .Controls(3).Style = msoButtonCaption
            .Controls(3).OnAction = "SortCastles"
            .Controls(3).Caption = "Sort Castles"
            .Controls(3).TooltipText = "Sorts the Castles into Alphabetical Order"
            
            .Controls(4).Style = msoButtonCaption
            .Controls(4).OnAction = "xmap"
            .Controls(4).Caption = "X all Freeform"
            .Controls(4).TooltipText = "This option resets the freeform map"
            
            .Controls(5).Style = msoButtonCaption
            .Controls(5).OnAction = "CalcSheet"
            .Controls(5).Caption = "Calculate Sheet"
            .Controls(5).TooltipText = "This option calculates the sheet"
            
            
        End With
        
        
        '*********************************************************************************
        ' Add "Dividers"
        '*********************************************************************************
        .Controls(2).BeginGroup = True
        .Controls(4).BeginGroup = True
        .Controls(5).BeginGroup = True
        .Controls(8).BeginGroup = True
        
       For i = 1 To 8
            .Controls(i).Width = 100
        Next
               
               
    End With
    
    '*************************************************************************************
    ' Display the commandbar - by default it should display on a single line
    ' (even if floating) - you should be able to play with height and width to
    ' change this - here the width is 100 (buttons are 90) so this forces height
    ' its actually the last parameter that changes the display (so although the
    ' following gives height as the width is just over the button size, if you
    ' define height as 50 it will override the width command and the toolbar will
    ' go back to a single line)
    ' You'll get an error with some of these values if the toolbar is not "floating"
    '*************************************************************************************
    CBar1.Width = 100
    'CBar1.Height = 50
    CBar1.Top = 500
    CBar1.Left = 550
    CBar1.Visible = True
    Set CBar1 = Nothing

End With
Exit Sub
Errorhandler:
'*************************************************************************************
' Most likely error is setting width/ height of a non-floating toolbar.
'*************************************************************************************


Select Case Error
    Case "Method 'Width' of object 'CommandBar' failed"
        Message = "Toolbar is not floating - cannot vary width" & vbCrLf & vbCrLf & _
        "Change toolbar position?"
        response = MsgBox(Message, vbYesNo + vbQuestion, "Error")
        Select Case response
            Case vbYes
                CBar1.Position = msoBarFloating
                Resume Next
            Case vbNo
                Resume Next
        End Select
    Case "Method 'Height' of object 'CommandBar' failed"
        Message = "Toolbar is not floating - cannot vary height" & vbCrLf & vbCrLf & _
        "Change toolbar position?"
        response = MsgBox(Message, vbYesNo + vbQuestion, "Error")
        Select Case response
            Case vbYes
                CBar1.Position = msoBarFloating
                Resume Next
            Case vbNo
                Resume Next
        End Select
    Case Else
        MsgBox "Unexpected error: " & Error
        
End Select



End Sub
Sub Auto_Close()
For Each c In CommandBars
    If c.Name = TbarName Then
        c.Delete
    End If
Next

End Sub

With this code to hide the menu if another *workbook* is activated:

Private Sub Workbook_Activate()
'************************************************************************************
' This event makes the custom menu visible when workbook is active
'************************************************************************************
On Error Resume Next
With Application.CommandBars(TbarName)
    .Visible = True
End With

End Sub

Private Sub Workbook_Deactivate()
'************************************************************************************
' This event hides the custom menu when another sheet is active
'************************************************************************************
On Error Resume Next
With Application.CommandBars(TbarName)
    .Visible = False
End With

End Sub

For some reason, when I open the *workbook from Explorer* as a stand alone *workbook* it is fine and the menu appears as it is supposed to, but when I call it from the *menu Workbook* it does not open.

Anyone have any ideas ?

 

[Nimrod]

Absolution:
At a quick glance I'm thinking the problem may be in the macro in the "other " sheet. You may need to have that macro Prefix the name of the macro with the name of the sheet module it's located in .


FOR EXAMPLE:
If this was my macro in Sheet1's code module..
Public Sub demoSh1()
MsgBox "Sheet1"
End Sub

Then this is what I would use to call it from sheet2
Public Sub SourceMacro()
Call Sheet1.demoSh1
End Sub

 

[Absolution]

Sorry, I think I gave the problem slightly wrong. This is in separate workbooks not sheets.

Anyway, I have tried a few variation on the above and it doesn't work at the moment.

Any thoughts on tweaking the above as I suspect your idea is the best workaround for this.

 

[Absolution]

Right, got it working, sort of, by adding the code

Application.Run "'Artefact Rumours.xls'!Autpen"

but it doesn't open every time !!!!

 

[Absolution]

Because I have to tell it to run the auto_Close routine as well !!!

Why does it not do these automatically from Macros ? Anyone know ?

 

[Richie(UK)]

Hi A,

The Workbook_Open event of the ThisWorkbook object will fire when the workbook is opened by automation (ie from another workbook). However, when a workbook with Autpen is opened by automation then the code does not fire.

You can force it to run by using the RunAutoMacros instruction. Like this:

Sub Test()
    Dim wbk As Workbook
    
    Set wbk = Workbooks.Open("C:\ABook.xls")
    wbk.RunAutoMacros which:=xlAutoOpen

End Sub

That said, you may find it simpler in the long run to move to the workbook event approach instead.

HTH

 

[Absolution]

New problem with this

First of all, I didn't know the last post had been put on, so thanks Richie !!!

I am using the above code, with the slight alterations, on several sheets.

When I close one sheet and it "goes back" to one with the toolbar in, it whinges about "application-defined or object-defined error". Thi just seems to affecting the activate macro to make the manu bar visible as if I switch to another sheet and then go back, the menu appears again.

I have changed the global constants in the sheets so that the names are different, and no problems occur until closing the sheets.

Any thoughts people.

 

[XLGibbs]

Instead of toggling the visible property, toggle the enabled property and see if that resolves it.

The enabled property relates to the toolbar being "checked" in the view>toolbars menu.

If the visible property is set to false, the application may be getting the error because this would also "uncheck" the toolbar, making it an invalid object reference in the current scope.

Just my thoughts, I isn't no expert.

 

[Absolution]

Thanks for the suggestion Gibbs, it was worth a shot !

Still getting the error. Have just replace the visible in the activate, deactivate to enabled (In case that wasn't what you meant)

I am going to try sleep in case that helps. Unfotunately I have so far tried Drinking, Sleeping, Eating and having a long bath as possible solutions to VBA problems. This one isn't jumping out as solvable through irrational methods, so I am having to try the good old fashioned way and ask people lol !!

I am wondering if the issue is more the calling up of the activate macro in the sheet, as it is not really going TO the sheet (activate), so much as ending up there because the thing in front disapeared !!! ( And if that made sense, then you are very clever LOL !)

 

[XLGibbs]

I just noticed, and perhaps it is the detail bastard in me, but you are referring to different SHEETS in your written descriptions and questions and commented parts of your code, but the code is the WORKBOOK activate/deactivate code...

If you want the toolbar to toggle in the same workbook but different sheets then put your toggle code in the Sheet event codes...