Custom Menu on opening workbook

xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Sort by date Sort by votes
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.
 
Upvote 0
Hello,

You can call the sheetcode from workbook_open event
Code: 
Private Sub Workbook_Open()
Sheet1.Worksheet_Activate
End Sub

Only requirement: replace "Private" by "Friend" in the sheetcode
Code: 
Friend Sub Worksheet_Activate()
MsgBox "hello"
End Sub

kind regards,
Erik
 
Upvote 0
Thanks for the quick replies, Mike and Erik! I'm in good shape now. Best new year's wishes to all. AB
 
Upvote 0
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):

Code: 
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.
 
Last edited:
Upvote 0
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:

Code: 
For each ws in Worksheets
ws.Worksheet_Activate
Next ws

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

But the same statement works on a single sheet:
Code: 
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
 
Upvote 0
1.
Code: 
ws.Worksheet_Activate
"ws" is the worksheet itself = the object
2.
Code: 
Sheet1.Worksheet_Activate
Sheet1 is refering to the module Sheet1

this works for me
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
775
Logit
Logit
F
  • Question Question
Error when closing Workbook with a sheet list box
Replies
1
Views
99
Artik
Artik
I
  • Solved
Clear checkbox when workbook open advice
Replies
5
Views
256
smozgur
smozgur
P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
357
Pacman52
P
J
  • Question Question
Inactive sheet not updating
Replies
5
Views
147
JulianvO
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top