• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2833
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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