asked on

Private Sub Worksheet_Activate() not working when activated from another workbook

The attached 'Private Sub Worksheet_Activate' code is in my 'Combined' worksheet object in wb2. It works fine if i open that worksheet outside vba. However, it doesn't work when I open the workbook (wb1) & then activate that worksheet (Combined) from another wb2.

Wb2 does 2 things around it: (a) makes 'Combined' visible, and (b) activates the 'Combined' worksheet < this works fine.

Just in case it helps, I have posted the bit of code you might want to see where I maximise wb1 & activate 'Combined' worksheet.

Any recommendations please?

Application.ScreenUpdating = True 
' The following code maximises the minimised AMT workbook and also activates 'Combined' worksheet

ActiveWindow.ActivateNext
ActiveWindow.WindowState = xlMaximized

Sheets("Combined").Activate
Cells.Range("a4").Select

Open in new window

Private Sub Worksheet_Activate()
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl
     
Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "&AMT Service Request" 'names the menu item
        With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "AMT Input Form" 'adds a description to the menu item
            .OnAction = "F_OpenAMTInputForm"
            .FaceId = 593 'assigns an icon to the dropdown
        End With

    End With
End Sub

Open in new window

Rory Archibald

Are you sure that sheet wasn't already active?

Sandesh555

Instead of using Private Sub Worksheet_Activate() use only Sub.

When you use private sub. The macro becomes limited the the workbook in which it is their.

kazmdav

ASKER

Thank you for your responses. I've taken a look at Savant's thoughts & although I copy to that sheet from wb2 I don't actually activate until last (ie; in the code previously posted). I removed the 'Private', per Sandesh555 suggestion, & it's still not working.

(As per usual) I've spent way too much time trying to resolve this, so as a workaround, I've put the code from "Sub Worksheet_Activate" into a separate sub in wb1.

I now need to call that sub (named CC_MenuStartup) from wb2. I cannot yet figure out how to call CC_MenuStartup that resides in wb1 from wb2. Can you please advise?

Rory Archibald

Basically:

Application.Run "'" & wb1.name & "'!CC_MenuStartup"

Open in new window

kazmdav

ASKER

Thanks Rorya. I should have stated this earlier.....There's a problem in that my wb1 name is always different (ie; it has date & time of creation appended to the filename). I suspect I may need to do something about getting 'Thisworkbook.name' (or something like that) to use with the code you suggested?

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kazmdav

ASKER

I used activeworkbook.name in the run code & all is working well now.

Thank you Rorya.