Add standard excel button to custom toolbar menu

Add standard excel button to custom toolbar menu

Post by R2l4eGVyX0 » Fri, 23 Dec 2005 01:10:05


i all,

i have the following code that creates my custom toolbar.
I would like to add some standard toolbar buttons to it, but am having
problems,

The buttons i want to add are:

Save
Print Preview
Print
Zoom
Center
Borders
Fill Color
Font Color

I tried to record a macro adding the buttons i wanted, but it gave me code
like:

Application.CommandBars("Custom Popup 942187").Controls.Add
Type:=msoControlSplitButtonPopup, ID:=401, before:=1

and this never worked when the code was run again - i'm thinking that
"Custom Popup 942187" is a temporary ID for the target menu.

I was thinking I could add these buttons to the "Workbook Tools" menu in the
With MenuItm
If Arr0(i) = "Wor&kbook Tools" Then
'.Type = Arr2(i)(j)
'.ID = Arr4(i)(j)
'.Style = Arr2(i)(j)
.FaceId = Arr4(i)(j)

Else

section - however when i try to set the .type and .id, i get a 'cannot set a
read only property' error.

any thoughts?

tia

J

Here is my code:

Public Sub CreateToolbar()
' create the custom toolbar for this application
' Arr0 contains the names of the buttons created
' Arr1 contains the tooltip text for the buttons in Arr0
' Arr0 - Arr5 must be the same dimensions 1xY
' Arr2 contains an array of arrays with the names of the submenu items
' Arr2 - Arr5 are accessed by Arr#(i)(j), where # is 2-4 and i is the
ith element in
' Arr2 and j is the jth element in the inner array.
' Arr3 contains the names of the macros that the corresponding elements
in Arr2 will
' refer to.
' Arr4 contains the FaceId of each element in Arr2 (ie the button image)
' Arr5 contains the tags of the buttons, used to determine which one was
called (in lieu of passing arguments)

' Pre-dimension all variables that will be used
Dim CBAR As CommandBar
Dim NewMenu As CommandBarControl, MenuItm As CommandBarButton,
SubMenuItm As CommandBarControl
Dim Arr0 As Variant, Arr1 As Variant, Arr2 As Variant, Arr3 As Variant,
Arr4 As Variant, Arr5 As Variant, Arr6 As Variant
Dim i As Integer, j As Integer, widths As Integer
Dim MenuName As String

' Define the menu name - flexibility to change the name only in one spot
' activeMenu defined in CONSTANTS
MenuName = activeMenu
' If the toolbar exists, delete it and create it new.
' Used to prevent duplicates and erors
On Error Resume Next
Application.CommandBars(MenuName).Delete
On Error GoTo 0
' Global name defined in 'Constants' - used for extensability in the
future
' if further menus are needed (ie to turn on/off - delete the 'active'
menu

' set the widths of the menus - keeps them uniform
widths = 100

Call TurnOffUpdates(True)

' Define the arrays that will be used to create the custom toolbar
' to add an element, add an entry in each of the arrays below
' make sure that the inserted elements are all inserted in their
' corresponding slots. ie to add a new menu at position 0
' make sure that it is the first element in Arr0-Arr5
' Arr0 - the Display name of the top level menu
' Arr1 - the tooltip text of the top level menu
' Arr2 - the Display name of the sub-menu items
' Arr3 - the name of the macro that will be
 
 
 

Add standard excel button to custom toolbar menu

Post by Bob Philli » Fri, 23 Dec 2005 01:30:14

sn't your menu called by whatever value activeMenu holds?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gixxer_J_97" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
the
a
elements
image)
was
Variant,
spot
Filters",
"Fill
"View
"",
"resetLedgerFilterRanges",
"resetOrderFilterRanges",
"SwitchOut",
_



 
 
 

Add standard excel button to custom toolbar menu

Post by R2l4eGVyX0 » Fri, 23 Dec 2005 01:39:03

f you mean that if 'activeMenu' contains the string value "Toolbox", then
looking in to toolbars menu you will see 'Toolbox' there, then yes.


"Bob Phillips" wrote:

 
 
 

Add standard excel button to custom toolbar menu

Post by Bob Philli » Fri, 23 Dec 2005 02:30:06

o isn't that the commandbar you should use?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gixxer_J_97" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
code
in
set
items
the
one
one
the
'active'
"",
"View
"",
"",
"Refresh
Sales
"PublishDocuments",
1100),
2174,
"Work
"",
_
"Inventory",
the
top


 
 
 

Add standard excel button to custom toolbar menu

Post by R2l4eGVyX0 » Fri, 23 Dec 2005 02:47:02

am, the problem i am having is adding those standard excel buttons to the
menu called "Workbook Tools"


"Bob Phillips" wrote:

 
 
 

Add standard excel button to custom toolbar menu

Post by R2l4eGVyX0 » Fri, 23 Dec 2005 02:53:01

f i use this:
Application.CommandBars("Toolbox").Controls.Add
Type:=msoControlSplitButtonPopup, ID:=401, before:=1

it does work, however it adds the button to the top level of the toolbar,
not to the drop-down menu "Workbook Tools"


"Gixxer_J_97" wrote:

 
 
 

Add standard excel button to custom toolbar menu

Post by Bob Philli » Fri, 23 Dec 2005 03:19:43

o how about

Application.CommandBars("Toolbox").Controls("Workbook Tools)>.Controls.Add

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gixxer_J_97" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
the
"Toolbox", then
message
having
gave me
that
menu
'cannot
submenu
i is
corresponding
button
which
CommandBarButton,
As
in
extensability in
the
toolbar
below
their
used
Tools",
Sheet",
Ledger
Inventory
worksheet.",
for
Order", "",
Documents",
Ledger
Withdrawls",
Customer",
Journal",
"",
Ranges", "",
Summary"), _
"View
"View
"View
Options"), _
msoControlButton,
msoControlSplitButtonPopup,
"",
"SwitchOut",
"RemoveCustomer", "",
"FillSalesJournal",
"SwitchOut",
610, 1,
_
2010,
_
2174,
"",
"Work
"Ledger",
"Customers:A1", "",
Links"),
"Sales
top of
temporary:=False)
names
names


 
 
 

Add standard excel button to custom toolbar menu

Post by Tom Ogilv » Fri, 23 Dec 2005 03:30:11

ou need to add you controls to the Workbook Tools control:

Sub rrr()
With Application.CommandBars("Toolbox")
Set cp = .Controls.Add(Type:=msoControlPopup)
End With
cp.Caption = "WorkBook Tools"
With cp
.Controls.Add _
Type:=msoControlSplitButtonPopup, ID:=401, before:=1
End With
End Sub

As an illustration.

--
Regards,
Tom Ogilvy


"Gixxer_J_97" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
the
"Toolbox", then
message
having
gave me
that
menu
'cannot
submenu
i is
corresponding
button
which
CommandBarButton,
As
in
extensability in
the
toolbar
below
their
used
Tools",
Sheet",
Ledger
Inventory
worksheet.",
for
Order", "",
Documents",
Ledger
Withdrawls",
Customer",
Journal",
"",
Ranges", "",
Summary"), _
"View
"View
"View
Options"), _
msoControlButton,
msoControlSplitButtonPopup,
"",
"SwitchOut",
"RemoveCustomer", "",
"FillSalesJournal",
"SwitchOut",
610, 1,
_
2010,
_
2174,
"",
"Work
"Ledger",
"Customers:A1", "",
Links"),
"Sales
top of
temporary:=False)
names
names


 
 
 

Add standard excel button to custom toolbar menu

Post by R2l4eGVyX0 » Fri, 23 Dec 2005 03:41:17

ou know what the most frustrating thing is? i tried that and it gave me an
error. apparently i missed something...

thanks Bob!

J

"Bob Phillips" wrote:

 
 
 

Add standard excel button to custom toolbar menu

Post by Bob Philli » Fri, 23 Dec 2005 04:13:39

y error

Application.CommandBars("Toolbox").Controls("Workbook Tools).Controls.Add

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gixxer_J_97" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
an
Tools)>.Controls.Add
toolbar,
to
message
yes.
am
it
thinking
Tools"
a
and
the
determine
Arr3
only
delete
custom
in
0
will be
"Chan&ge
"Tools
Line",
Deposits",
List",
"CancelOrder",
_
"SwitchOut",
4,
2174),
1,
2174),