[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2356
  • Last Modified:

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

0
kazmdav
Asked:
kazmdav
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
Are you sure that sheet wasn't already active?
0
 
Sandesh555Commented:
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.
0
 
kazmdavAuthor Commented:
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?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rory ArchibaldCommented:
Basically:
Application.Run "'" & wb1.name & "'!CC_MenuStartup"

Open in new window

0
 
kazmdavAuthor Commented:
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?
0
 
Rory ArchibaldCommented:
If it's a workbook you open in your code, then you can assign it to a variable at that point. If not, then you can use Activeworkbook.name if it is the active workbook, or Thisworkbook.name if it's the workbook containing the code.
0
 
kazmdavAuthor Commented:
I used activeworkbook.name in the run code & all is working well now.

Thank you Rorya.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now