disabling a toolbar when no workbook is active

[Koesper]

Hi!

I've built a tool (with quite a lot of help by you guys!) which uses a custom toolbar to call a userform.
This works like a charm, but i want to disable the toolbar when there is no active worksheet, so it behaves just like a 'standard' toolbar, it's greyed-out whenever there is nothing to work with.


disabling the toolbar itself would be something like this:
[vba]
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")

Dim myCommandButton As CommandBarButton

For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = false
Next
[/vba]


but i've got no idea where i should put this piece of code
is there an event like application.workbook_open or something?

and should i do this in a 'do...while' loop?

The last couple of weeks i've learned quite a lot of VBA,my tool is almost complete, my users are happy, and i'm proud like a ... eh.. something that's very proud...
(I dont know if there is a english saying or something.. if i translate it from dutch it would be proud as a peacock... but i'm getting offtopic)

But in the tool i've built, i do very little with events and i've managed to completely avoid "do...while" loops, i'm using "for...next" or "for...each" mostly.

What i'm saying, is that i have not a clue where to start...
So.... can anyone point me in the right direction?

Thanks a bunch!

Regards,
Casper
the Netherlands

[Bob Phillips]

[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")

Dim myCommandButton As CommandBarButton

If Windows.Count = 1 Then
For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = False
Next
End If
End Sub[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Bob Phillips]

BTW it is as proud as a peacock in English too, or as pleased as Punch

[Koesper]

(well.. this is a deja-vu, this is the second time i write this reply, because when i wrote it the first time the forumdatabase was unavailable...)

xld, thanks again for your help!
I've tried your suggestion, but that doesnt quite do what i need it to do.
this event is only triggered when the workbook where the code is in is closed.
and not every time a workbook in the application is closed.

is there another event that i could use? or is there another way?
(except for checking the file after a button is pressed)

[Bob Phillips]

(well.. this is a deja-vu, this is the second time i write this reply, because when i wrote it the first time the forumdatabase was unavailable...)

xld, thanks again for your help!
I've tried your suggestion, but that doesnt quite do what i need it to do.
this event is only triggered when the workbook where the code is in is closed.
and not every time a workbook in the application is closed.

is there another event that i could use? or is there another way?
(except for checking the file after a button is pressed)

I assumed that when yoiu said when no workbook, was available you meant none open. Try this alternative

[vba]
Private Sub Workbook_Activate()
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")

Dim myCommandButton As CommandBarButton

For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = True
Next
End Sub

Private Sub Workbook_Deactivate()
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")

Dim myCommandButton As CommandBarButton

For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = False
Next
End Sub
[/vba]