Koesper
02-10-2006, 02:46 AM
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:
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")
Dim myCommandButton As CommandBarButton
For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = false
Next
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
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:
Dim myCommandBar As CommandBar
Set myCommandBar = Application.CommandBars("myCustomCommandBar")
Dim myCommandButton As CommandBarButton
For Each myCommandButton In myCommandBar.Controls
myCommandButton.Enabled = false
Next
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