How to reset Worksheet Menu Bar in VBA???

  • Thread starter Thread starter Simon Lloyd
  • Start date Start date
S

Simon Lloyd

Hi, I want to be able to reset the worksheet menu bar on auto close s
that it has its default settings, i already have an autoclose macr
which turns command bars back on and calculation etc.

Does anyone have a one liner i can insert to make this happen??

Thanks,

Simo
 
Just some added info. (if by autoclose you mean the beforeclose event in
the thisworkbook module).
In the berforeclose event you might need to preface commandbars with
application.

Application.CommandBars("Worksheet Menu Bar").Reset
or
Application.CommandBars(1).Reset

--
Regards,
Tom Ogilvy

Vasant Nanavati said:
CommandBars(1).Reset
Click to expand...
 
You may want to keep track of your changes and just undo them. I'd hate to have
another program reset my toolbar.

I'd lose all my customizations!
 
I have this code which creates a button on the worksheet menu bar but i
isnt deleted when i run my autoclose program thats why i wantes to rese
the menu bar, if either of you know of a nicer way to manage this rathe
than be aggressive and put everything back to normal please let m
know.

Simon.

Here's the code!

Sub en()
Dim c As Variant
On Error Resume Next
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .CommandBars("Worksheet menu Bar").Controls
If c.Caption = "EN" Then c.Delete
Next c
Set cb = .CommandBars("Worksheet Men
Bar").Controls.Add(Type:=msoControlButton, Id:=2950, before:=1)
cb.Caption = "EN"
cb.TooltipText = "Enable Events"
cb.OnAction = ThisWorkbook.Name & "!enevents"
cb.Style = msoButtonCaption
Worksheets("hidden").Visible = True

End With
End Sub

Sub enevents()
Application.EnableEvents = Not Application.EnableEvents
End Su
 
What was the name of your auto_close macro?

This is a pretty standard way of doing it:

Option Explicit

Sub auto_open()
Call EN
End Sub

Sub EN()

Dim cb As CommandBarControl

With Application
Call CleanUpMenuBar
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlButton, ID:=2950, temporary:=True, before:=1)
With cb
.Caption = "EN"
.TooltipText = "Enable Events"
.OnAction = ThisWorkbook.Name & "!enevents"
.Style = msoButtonCaption
End With
Worksheets("hidden").Visible = True
End With
End Sub

Sub enevents()
Application.EnableEvents = Not Application.EnableEvents
End Sub

Sub CleanUpMenuBar()

On Error Resume Next
Application.CommandBars("Worksheet Menu bar").Controls("EN").Delete
On Error GoTo 0

End Sub

Sub auto_close()
Call CleanUpMenuBar
End Sub

Notice that I added temporary:=true to the .add.

It won't clean up the menubar when you close the workbook, but if you close
excel and reopen it, it won't appear on the menubar. (Well, until you open the
workbook that modifies the menubar.)
 
Thanks Dave,

In fact i dont call on the EN sub in the autoclose, it is called whe
admin enter their password for their sub to give them differen
options.......being daft i thought it would not appear in any othe
work book.

But i will of course be plageristic and copy your code <g>!

Once again thanks.

Simo
 
shhhh.

That's how I got it, too!



Simon Lloyd < said:
Thanks Dave,

In fact i dont call on the EN sub in the autoclose, it is called when
admin enter their password for their sub to give them different
options.......being daft i thought it would not appear in any other
work book.

But i will of course be plageristic and copy your code <g>!

Once again thanks.

Simon
Click to expand...
 
Back
Top