Having a custom toolbar open with workbook

[rob nobel]

I would like to know if there's a way to have a workbook open and a custom
toolbar also opens. Then, when the workbook closes, the default (or
remaining) toolbars are left so that the custom toolbar closes with the
closing of the workbook.
Or better still.... that the default toolbar (whatever opens upon opening
Excel) is replaced with the new toolbar on opening that workbook.
The main reason I want to do this is because I do not want buttons
positioned all over the worksheets (not enough room and gets a bit messy) as
I want special buttons to be available when accessing different worksheets
within that workbook and other workbooks.
Thanks,
Rob

 

[Nikos Yannacopoulos]

Rob,

Have a look at my reply to Morten's posting of Nov.06,
2:34AM, titled "VBA - Excel Setup", in the same newsgroup.
I trust it will give you a good idea of how to manipulate
toolbars.
Put some similar code in the Open and BeforeClose code
sections of the workbook and the job is done!

Nikos Y. (nyannacoi at in dot gr)

 

[rob nobel]

Hi Nick,
Thanks for that info.
BUT.... I put this in to the workbook open event as follows and the message
"Object variable or With block variable not set." comes up for the line:
For i = 1 To CommandBars.Count

Private Sub Workbook_Open()
'Check and store visible
Open "C:\Windows\temp\toolbars.txt" For Output As #1
For i = 1 To CommandBars.Count
If CommandBars(i).Visible = True Then Print #1, CommandBars(i).Name
Next
Close #1
'Hide visible
Open "C:\temp\toolbars.txt" For Input As #1
Do Until EOF(1)
Line Input #1, tlbr
CommandBars(tlbr).Enabled = False
Loop
Close #1
End Sub

I've tried to look at what Helps has to offer, but I do not understand what
set variable, etc. means.
Can you please assist further?
Rob

 

[Dave Peterson]

Try changing all the commandbars to application.commandbars

And I'd bet that the path changed by accident (windows\temp vs. \temp).

 

[rob nobel]

Thanks Dave!
Seems to have done the trick??? as the workbook openened OK... BUT... I did
not yet specify a custom toolbar to the workbook and after running the code,
ALL my tool bars are missing including the menu bar and I can't find a way
to restore them.
AAAGGHHHHH!! Please help!
Rob

 

[rob nobel]

Found my menubar doing
Sub test()
Application.CommandBars(1).Enabled = True
End Sub
BUT I can't find my custom toolbars anywhere although they are listed when
I click the Customise/attach.
Rob

Thanks Dave!
Seems to have done the trick??? as the workbook openened OK... BUT... I did
not yet specify a custom toolbar to the workbook and after running the code,
ALL my tool bars are missing including the menu bar and I can't find a way
to restore them.
AAAGGHHHHH!! Please help!
Rob

 

[Dave Peterson]

I'm not sure if this is it, but did you look under Tools|Customize and see them
there without a checkmark?

Maybe they're just not visible.

(Everytime I've clicked on the Attach button, I think I've seen my "custom 1"
and "custom 2" toolbars--but I don't do it enough to notice any problems.)

Found my menubar doing
Sub test()
Application.CommandBars(1).Enabled = True
End Sub
BUT I can't find my custom toolbars anywhere although they are listed when
I click the Customise/attach.
Rob

 

[rob nobel]

What's strange is that I created some custom toolbars which are no longer
shown in Tools/Customize section......Yet, if I try to recreate that
toolbar using the same name it tells me that name already exists. So
where's it hiding??
This only happened to me after running the code (earlier in this message
stream) to delete existing toolbars so a workbook will open with a custom
toolbar instead.
Trouble with it is that the code also deletes the menubar which seems to
hold the Custom toolbar references???
But I'm still interested to know where they went if Excel still tells me
that they're there (or at least their names are still remembered).
Rob

 

[Dave Peterson]

I did some more playing around.

I created a temporary toolbar and ran code like:

application.commandbars("test99").enabled = false

Then I looked at the Tools|customize list and it wasn't there.

So you could try enabling the toolbar that you know is there
application.commandbars("yourtoolbarname").enabled = true

or even get 'em all at once:

Option Explicit
Sub testme()

Dim myCB As CommandBar
For Each myCB In Application.CommandBars
myCB.Enabled = True
Next myCB

End Sub

Maybe more careful:

Option Explicit
Sub testme2()

Dim myCB As CommandBar
For Each myCB In Application.CommandBars
If myCB.BuiltIn Then
'do nothing
Else
myCB.Enabled = True
End If
Next myCB

End Sub

But I didn't notice a difference between the two (cursory glance, though) after
running both consecutively.

And you should know that excel stores these customized toolbars (not attached to
a workbook and not added/deleted on the fly) in a file called *.xlb (name and
location varies with the version of windows and excel).

But you'd do yourself a favor by finding it and making a backup. Then you can
always do as much playing around as you want and you won't lose anything by
deleting the "real" *.xlb and replacing it with the back up version.

And this file can get corrupted, too (another good reason to have a backup). A
few years ago my *.xlb was in the 450k range. I deleted it and recreated it
from scratch. I added a couple icons and deleted a few I didn't use. The new
file size was 11k.

Now I copy the backup onto the "real" every so often. Sometimes, when this file
is bloated/corrupted, excel has problems (slow opening/slow closing, and other
weird stuff.)

(and good morning <vbg>)

 

[rob nobel]

Wow! Great info! Thanks Dave, I will implement all these suggestions as
they're very helpful.
(Are you 'Excel nuts' not married keeping such rediculous hours?)
Rob

 

[Dave Peterson]

Glad it worked for you.

And I ain't answering that question. (And neither are the voices inside my
head!)

 

[rob nobel]

Well said!
Rob
Ps. My regards to the wife.

Glad it worked for you.

And I ain't answering that question. (And neither are the voices inside my
head!)