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

Gixxer_J_97 wrote:
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

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

"Bill Martin" wrote:

Gixxer_J_97 wrote:
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

Congrats on figuring it out!

Bill
-------------------------
Gixxer_J_97 wrote:
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

"Bill Martin" wrote:


Gixxer_J_97 wrote:

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