Command Bars - working with the built in buttons

[Guest]

Hi all,

One quick specific question re one wee oddity I have noted with the
following line of code:

Dim btnLB3 As CommandBarButton
Set btnLB3 =
Application.CommandBars("SLBTbar").Controls.Add(Type:=msoControlButton,
ID:=1561)

which adds the "View Code" button to my custom toolbar "SLBTbar".

However once added it is disabled when I try to use it from the workbook I
created it from, but it works fine when other workbooks are activated.

Sure this is easy peasy and I am just being stoopid.


I have a general question surrounding this whole area - we have a problem at
work which we have been told is due to a key add-in not being able to cope
with an excel11.xlb file bigger than 30KB. Obviously this severely limits the
amount of customisation we can do to our toolbars. Equally obviously our IT
bods are cretins. I am not an IT bod <grin>.

I have seen the Debra Dalgleish approach using an add-in to creating custom
toolbars on the fly and also the John Walkenbach approach to saving and
retrieving menu settings to a worksheet, and realise it would not be rocket
science to create a "save and retrieve" functionality for the whole
menu/toolbar structure outside of the xlb file.

There are obviously a number of questions:

1. Is it likely IT Dept talking sense with respect to a large add-in falling
over if the xlb file is too big? (i.e we can have an extensively customised
menu or the functionality of the add-in but not both).
2. If so has anyone already designed a fully fledged workaround to save all
a users menu/toolbar settings outside of the xlb file.
3. Any general pointers (obviously I am just messing around learning how
this stuff works at the moment)

Cheers,

 

[Jim Rech]

However once added it is disabled

I wasn't able to duplicate this. Unless of course the active sheet was a
chart sheet or an XL4 macro sheet or a dialog sheet...

Seems unlikely but I suppose it's easy to test if it's true. A workaround
is to make your menu/toolbar customizations via macros in your Personal.xls,
deleted when Personal closes or at least with the commandbars 'temporary'
parameter set. That way they never hit the XLB.

--
Jim
| Hi all,
|
| One quick specific question re one wee oddity I have noted with the
| following line of code:
|
| Dim btnLB3 As CommandBarButton
| Set btnLB3 =
| Application.CommandBars("SLBTbar").Controls.Add(Type:=msoControlButton,
| ID:=1561)
|
| which adds the "View Code" button to my custom toolbar "SLBTbar".
|
| However once added it is disabled when I try to use it from the workbook I
| created it from, but it works fine when other workbooks are activated.
|
| Sure this is easy peasy and I am just being stoopid.
|
|
| I have a general question surrounding this whole area - we have a problem
at
| work which we have been told is due to a key add-in not being able to cope
| with an excel11.xlb file bigger than 30KB. Obviously this severely limits
the
| amount of customisation we can do to our toolbars. Equally obviously our
IT
| bods are cretins. I am not an IT bod <grin>.
|
| I have seen the Debra Dalgleish approach using an add-in to creating
custom
| toolbars on the fly and also the John Walkenbach approach to saving and
| retrieving menu settings to a worksheet, and realise it would not be
rocket
| science to create a "save and retrieve" functionality for the whole
| menu/toolbar structure outside of the xlb file.
|
| There are obviously a number of questions:
|
| 1. Is it likely IT Dept talking sense with respect to a large add-in
falling
| over if the xlb file is too big? (i.e we can have an extensively
customised
| menu or the functionality of the add-in but not both).
| 2. If so has anyone already designed a fully fledged workaround to save
all
| a users menu/toolbar settings outside of the xlb file.
| 3. Any general pointers (obviously I am just messing around learning how
| this stuff works at the moment)
|
| Cheers,
|
|
|