Custom toobar, assigning icons under VBA control. 1

[cresbydotcom]

OK I can assign icons manually but how to assign them in a macro.

I loose toolbars occasionally and rather than chase the rare event I can re-instate them with:

    Application.CommandBars.Add(Name:="tester").Visible = True
    Application.CommandBars("tester").Controls.Add _ Type:=msoControlButton, ID:= 2950, Before:=1
      With Application.CommandBars("tester").Controls.Item(1)
       .Caption = "CleanWL"
       .Style = msoButtonCaption
       .OnAction = "'C:\My Documents\zcodez3.xls'!rowdn"
      End With

etc

but nothing seems to be appropriate for the icon - well none that will change in the watch window or even read as a single property (as opposed to an expanded list)

So is this a dead end? Or am I missing something.

TIA


there is a tide in the affairs of man that you Cnut ignore.................

 

[borman2005]

Is it for 2003 or 2007?

 

[cresbydotcom]

Silly Cresbydotcom

Win XP
Excel 2002

Though I am on 2003 at home.

there is a tide in the affairs of man that you Cnut ignore.................

 

[borman2005]

This works for me in 2003:

Set myBar = Application.CommandBars("Testing")
Set myControl = myBar.Controls _
.Add(Type:=msoControlButton, Before:=3)
With myControl
.FaceId = 2950
.OnAction = app & "!rowdn"
.DescriptionText = "Description Here…"
.Caption = "Caption Here…"
.Style = msoButtonIconAndCaption
End With

First open the macro spreadsheet and run this within.
The button should stay after you close Excel.

 

[cresbydotcom]

in Excel 2002 FaceID cannot be altered in the watch window
and ID= 2950 is the default value from "record".

And more importantly all items have a value of 2950, this changes to 481 if you set, say, a heart as the icon.

So knowing one number (2951 gives no icon in the .FaceId = line). I tried ID=481 in the add command and it works there too (2951 gave an error there).
now to find the rest - should be easy - except I can't do it in the watch window.

Thanks for that - I tried others like InstanceId and look down in picture but when do you stop?.

there is a tide in the affairs of man that you Cnut ignore.................

 

[cresbydotcom]

well
481 works and 279 but 38 causes an error in ID= 38
run time error & unspecified error

OK as .FaceID = 38 though

So a success.

Thanks have a gold star.

there is a tide in the affairs of man that you Cnut ignore.................

 

[borman2005]

No problem. Glad to help.