How to reset Worksheet Menu Bar in VBA???
 

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

--
Message posted from http://www.ExcelForum.com

How to reset Worksheet Menu Bar in VBA???
 

CommandBars(1).Reset

--

Vasant

"Simon Lloyd " wrote in message
...
Hi, I want to be able to reset the worksheet menu bar on auto close so
that it has its default settings, i already have an autoclose macro
which turns command bars back on and calculation etc.

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

Thanks,

Simon


---
Message posted from http://www.ExcelForum.com/

How to reset Worksheet Menu Bar in VBA???
 

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" <vasantn *AT* aol *DOT* com wrote in message
...
CommandBars(1).Reset

--

Vasant

"Simon Lloyd " wrote in
message
...
Hi, I want to be able to reset the worksheet menu bar on auto close so
that it has its default settings, i already have an autoclose macro
which turns command bars back on and calculation etc.

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

Thanks,

Simon


---
Message posted from http://www.ExcelForum.com/

How to reset Worksheet Menu Bar in VBA???
 

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!



"Simon Lloyd <" wrote:

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

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

Thanks,

Simon

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

How to reset Worksheet Menu Bar in VBA???
 

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, befo=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

--
Message posted from http://www.ExcelForum.com

How to reset Worksheet Menu Bar in VBA???
 

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, befo=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.)




"Simon Lloyd <" wrote:

I have this code which creates a button on the worksheet menu bar but it
isnt deleted when i run my autoclose program thats why i wantes to reset
the menu bar, if either of you know of a nicer way to manage this rather
than be aggressive and put everything back to normal please let me
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 Menu
Bar").Controls.Add(Type:=msoControlButton, Id:=2950, befo=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 Sub

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

How to reset Worksheet Menu Bar in VBA???
 

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

--
Message posted from http://www.ExcelForum.com

How to reset Worksheet Menu Bar in VBA???
 

shhhh.

That's how I got it, too!



"Simon Lloyd <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson