Command Bar & Workbook Deactivate

G

Gixxer_J_97

Hi all,

I am using custom command bars (a different one for each sheet in my workbook)

my question is, how do i get it to 'deactivate' when the workbook is changed?

i'm assuming the code would go in ThisWorkbook under
Private Sub Workbook_Deactivate()

End Sub

but i'm lost as to how to 1) deactivate the command bar and 2) know which
one to deactivate.

my bars are defined like this
Sheet 1:
Set cb = Application.CommandBars.Add("MyBar1", msoBarFloating)
Sheet 2:
Set cb = Application.CommandBars.Add("MyBar2", msoBarFloating)
etc

any help?

tia!

J
 
B

Bill Martin

Gixxer_J_97 said:
Hi all,

I am using custom command bars (a different one for each sheet in my workbook)

my question is, how do i get it to 'deactivate' when the workbook is changed?

i'm assuming the code would go in ThisWorkbook under
Private Sub Workbook_Deactivate()

End Sub

but i'm lost as to how to 1) deactivate the command bar and 2) know which
one to deactivate.

my bars are defined like this
Sheet 1:
Set cb = Application.CommandBars.Add("MyBar1", msoBarFloating)
Sheet 2:
Set cb = Application.CommandBars.Add("MyBar2", msoBarFloating)
etc

any help?

tia!

J
------------------

Below is a snippet of code I use to kill a custom tool bar. It is called from
the same Workbook_Deactivate() routine that you are using.

Sub DeleteToolBar()
Const TbarName As String = "YourToolbarName"
On Error Resume Next
CommandBars(TbarName).Delete
On Error GoTo 0
End Sub

Good luck...

Bill
 
G

Gixxer_J_97

Thanks Bill,

After a few tweaks i ended up with:

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars(activeMenu).Visible = False
On Error GoTo 0
End Sub

Private Sub workbook_activate()
On Error Resume Next
Application.CommandBars(activeMenu).Visible = True
On Error GoTo 0
End Sub
thanks! =)

which hides/unhides the menu as you switch back and forth between workbooks.

J
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top