Toolbar Specific to a Workbook file

[Gerard Sanchez]

Hi,

I have customized toolbar built applicable only to a specific workbook file.
I attached that toolbar to the workbook, by clicking Attach (simple enough I
thought) but how come when I open completely unrelated workbook, I still see
that custom toolbar??

Is there any other way around this, to have specific toolbar open only on a
specific workbook file?

Or is Attach the only way to do this?
What am I doing wrong?

Any thoughts Ideas on this?

 

[Gord Dibben]

You are best off creating the toolbar on workbook open then deleting on
close or de-activate.

See Debra Dalgleish's site for creating a toolbar.

http://www.contextures.on.ca/xlToolbar02.html

Instead of saving as an add-in, just save the workbook with the code for
only that one specific book.


Gord Dibben MS Excel MVP

 

[Gerard Sanchez]

Hi Gord,

Read what Dave wrote on add-ins but I think your suggestion of 'creating the
toolbar on workbook open then deleting on close or deactivate'
is my best alternative.

But I need help on the VBA part on this.

Thanks

 

[Gord Dibben]

Couple of ways to go about this.

Be aware that the Toolbar you have created is stored in your Excelxx.xlb
file and will open with any workbook whether attached to your workbook or
not.

1. Since you already have the Toolbar created you can simply hide or unhide
the Toolbar but users could then unhide it if they chose by View>Toolbars

Private Sub Workbook_Activate()
Application.CommandBars("mytest").Visible = True
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("mytest").Visible = False
End Sub

2. Detatch the Toolbar and delete it completely. Create a new Toolbar
through code when the workbook opens or activates. Delete it when you close
or de-activate the workbook.

It will then not be contained in your Excelxx.xlb file

Which way do you want to go? First method is easiest.

But I would suggest the second method since Excellxx.xlb can get corrupted
when gets too large.

The site I pointed you to shows how to create the Toolbar when the workbook
opens and delete when the workbook closes.

Edit to suit for your particular needs.

For some sample workbooks with code see Ole Erlandson's site.

http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars


Gord

 

[Gerard Sanchez]

Hi Gord,

The second option is my best recourse.
I've looked and and read on Ole Erlando's site and got some useful code
lines in there.

Thanks for your feedback and for pointing me into the right direction

Appreciate the help.

--Gerard

 

[Gord Dibben]

Thanks for the feedback

Good luck with the project.


Gord