Auto launch custom toolbar

  • Thread starter Thread starter geotso
  • Start date Start date
G

geotso

Hi

How to make a custom toolbar auto open/close *only* each time I
open/close a worksheet that is based on a specific template of mine?

In other words, I have a template with some macros in it, and I've
created a custom toolbar to run those macros. However, I don't want
the toolbar always visible, as happens when I click View > Toolbars >
MyToolbar. I want "MyToolbar" visible, only when I work with a
worksheet based on the specific teplate.

Some help please?

____________________
Merlin dying, to the Lady of the Lake:
"we lived our lives with passion and devotion"

-=- geotso -=-
(remove the TRAP to contact me)
 
You could use the worksheet_activate/_deactivate and the
workbook_activate/_deactivate to hide (or delete) and show (or rebuild) the
toolbars.

But I'm not sure how you'd know which workbooks/worksheets are based on what
template.

Once you create a new workbook, you lose any connection to the template that was
used.
 
according "Dave Peterson":
You could use the worksheet_activate/_deactivate and the
workbook_activate/_deactivate to hide (or delete) and show (or rebuild) the
toolbars.

But I'm not sure how you'd know which workbooks/worksheets are based on what
template.

Once you create a new workbook, you lose any connection to the template that was
used.
Click to expand...

Dave,

I've created two macros into my template, to show/hide a custom
toolbar named myToolbar as follows:

Sub openBar()
Application.CommandBars("myToolbar").Visible = True
End Sub
-------------------
Sub closeBar()
Application.CommandBars("myToolbar").Visible = False
End Sub

If I could make them autorun, I think that each workbook created from
this template will be able to show/hide myToolbar. If it is so, I need
a VBA script (I believe) to make the macros autorun, the first
"onWorkbook.Open" and the second "onWorkbook.Close". Is this can be done?

Thanks for your concern.
 
Back to the VBE.

Hit Ctrl-R to see the project explorer.
Expand your project to see all the objects--including ThisWorkbook.

Double click on this workbook to see where its code would be.

Use the dropdowns above the code window to choose Workbook (on the Left hand
side)

And chose Workbook_Activate from the right hand side.

You can either paste your line in there like:

Option Explicit
Private Sub Workbook_Activate()
Application.CommandBars("myToolbar").Visible = True
End Sub

or just call your procedure (it's in a General module, right):

Option Explicit
Private Sub Workbook_Activate()
call Open_Bar
End Sub

Then the same technique with workbook_deactivate and the other code.
 
according "Dave Peterson":
Back to the VBE.

Hit Ctrl-R to see the project explorer.
Expand your project to see all the objects--including ThisWorkbook.

Double click on this workbook to see where its code would be.

Use the dropdowns above the code window to choose Workbook (on the Left hand
side)

And chose Workbook_Activate from the right hand side.

You can either paste your line in there like:

Option Explicit
Private Sub Workbook_Activate()
Application.CommandBars("myToolbar").Visible = True
End Sub

or just call your procedure (it's in a General module, right):

Option Explicit
Private Sub Workbook_Activate()
call Open_Bar
End Sub

Then the same technique with workbook_deactivate and the other code.
Click to expand...

Dave,

The first method of yours works like a charm!
I don't know why the second doesn't (I've just tested it in a hurry),
but it doesn't matter at all, since I found what I was looking for :)

Thanks again
 
Portions snipped.

I think I know why the second didn't work.

I used Open_Bar in my example and you had OpenBar as the macro name. (I added
that extra underscore that screwed it up.)

Sorry,
 
according "Dave Peterson":
Portions snipped.

I think I know why the second didn't work.

I used Open_Bar in my example and you had OpenBar as the macro name. (I added
that extra underscore that screwed it up.)

Sorry,
Click to expand...

You don't have to :)
I saw it and I corrected it right from the begining, so it didn't
cause the problem. Maybe the fault is on my side, because as I said
I've just tested it in a hurry.
 
Back
Top