Custom Menu on opening workbook

[xenou]

Hi:

I have a custom toolbar menu that I'd like to have available only on Sheet1 (to avoid users running macros on the wrong sheet). Everything works fine except when starting Excel as a new session - the workbook will not display the custom menu even when the correct sheet is active when the workbook opens.

I have the menu coded in a sheet_activate / sheet_deactivate event.

NOTE: Activating the sheet in a workbook_open event does not help!! It appears I only have this issue when I start the excel application by clicking on the file icon and Excel is NOT currently open.

1) Is there a way to trigger the sheet_activation event on opening the workbook in a new Excel session?
2) Is there a better design approach?

'-------
Apropros of #2 I imagine I can: validate all macros for the correct sheet before any of them run (so as to be able to display the menu at all times), have a workbook open to a "welcome" sheet, forcing the user to switch to the sheet and thereby activate it. Other ideas? But I hope there is a simpler answer a la question #1 above.

TIA.

 

[mikerickson]

I can't get the Sheet_Activate to trigger when I switch from one workbook to another.

Try writting a MyToolbar routine that checks the ActiveSheet.Name before excicuting (perhaps reveal and hide branches) and have it called by both the Workbook_Activate and Workbook_SheetsActivate events in ThisWorkbook.

 

[erik.van.geit]

Hello,

You can call the sheetcode from workbook_open event

Private Sub Workbook_Open()
Sheet1.Worksheet_Activate
End Sub

Only requirement: replace "Private" by "Friend" in the sheetcode

Friend Sub Worksheet_Activate()
MsgBox "hello"
End Sub

kind regards,
Erik

 

[xenou]

Thanks for the quick replies, Mike and Erik! I'm in good shape now. Best new year's wishes to all. AB

 

[xenou]

Edit:

Mike, I hadn't really noticed that my toolbar is still there when switching workbooks. Also, I realized maybe I don't want my sheet event to fire if another sheet will be displayed, so...

Here's my complete code and hopefully it will cover all the bases (using sheet activate and deactivate procedures declared as Friend in Sheet1):

Private Sub Workbook_Activate()
    If ActiveSheet.Name = "Sheet1" Then
        Sheet1.Worksheet_Activate
    End If
End Sub
'-------------------------------
Private Sub Workbook_Open()
    If ActiveSheet.Name = "Sheet1" Then
        Sheet1.Worksheet_Activate
    End If
End Sub
'-------------------------------
Private Sub Workbook_Deactivate()
    Sheet1.Worksheet_Deactivate
End Sub
'-------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Worksheet_Deactivate
End Sub

The workbook is for other users so I'm trying to be careful with behavior -- for my own part, I'd just quickly click on another tab and back... My quick tests thus far look good on this code. Up until now I've used toolbars that are created by workbook events only, so this has been a great lesson. Thanks.

 

[xenou]

Erik,
After creating worksheet_activate events with "Friend" scope, I am surprised to find I cannot loop through the worksheets and trigger their activate events when I reference them in loops:

For each ws in Worksheets
ws.Worksheet_Activate
Next ws

For x = 1 to Worksheets.Count
Worksheets(1).Worksheet.Activate
Next x

But the same statement works on a single sheet:

Sheet1.Worksheet_Activate

Do understand why this is the case?

----------------------------------
Note: I was going to apply the above to this individuals problem, but settled on a straightforward activation of each sheet instead:
http://www.mrexcel.com/forum/showthread.php?t=329172


Regards, AB

 

[erik.van.geit]

1.

ws.Worksheet_Activate

"ws" is the worksheet itself = the object
2.

Sheet1.Worksheet_Activate

Sheet1 is refering to the module Sheet1

this works for me

Sub test()
Dim ws As Worksheet
 
    For Each ws In Worksheets
    Run ws.CodeName & ".Worksheet_Activate"
    Next ws
 
End Sub

will bug if there is any sheet without worksheet_activate

best regards,
Erik

 

[xenou]

Thanks - that's a great explanation.
AB