Build a Macro that places Icon in toolbox & assigns it to a macro

[dyana]

I would like VBA code that builds an icon and places it into the existing Excel toolbar. I have tried to record a macro that creates a custom macro and alters the color and display of the icon that was placed into the toolbar and assigned to a macro.

After recording this macro, I noticed that only the placing of the "happy face" macro icon, or customized macro button, was placed into the toolbar. The macro did not change the look of the icon and assign it to another macro that I had built, althought this is the whole process that was "recorded".

Does anybody know a way to customize an icon for the toolbar and attach it to a macro?

I need this to be coded in VBA because it will be attached to an existing VBA process.

Thanks,
dyana

 

[LSTAN]

Hi dyana

You can change the icon of the button by using the control.FaceId properties, here is how

Sub Autpen()

Dim newBar As CommandBarControl, con As CommandBarControl

Set newBar = CommandBars.Add(Name:="YourToolBarName", _
Position:=msoBarTop, Temporary:=True)

newBar.Visible = True

Set con = newBar.Controls.Add(Type:=msoControlButton)

With con
.FaceId = 17 'the icon is set here, in this case it display the create chart icon face.
.OnAction = "RunMyMacro"
End With

End Sub


Hope this give you an idea.

rgrds
LSTAN

 

[dyana]

Hello LSTAN,

I appreciate your response.

I tried the code and it gave me a run-time error 5 response of "Invalid procedure call or argument".

The part of the code that generated this responses was:

Set newBar = CommandBars.Add(Name:="YourToolBarName", _
Position:=msoBarTop, Temporary:=True)

Do you have any ideas?

 

[LSTAN]

Hi dyana

Sorry typo error at


Dim newBar As CommandBarControl, con As CommandBarControl


it should be


Dim newBar As CommandBar, con As CommandBarControl


Let me know if you still encounter problem

rgrds
LSTAN

 

[dyana]

Hello LSTAN,

I am now getting back to the project that prompted the above question. I tried the revised code above and it worked and gave me the "Chart icon". Thanks.

I have a question, though. I tried to modified the number shown in the "Faceid" part of the code to generate a different looking icon within the toolbar. The faceid code that I tried to use was 1142 instead of 17. I tried to simply change the

With con
.FaceId = 17
.OnAction = "RunMyMacro"
to

With con
.FaceId = 1142

but it does not work.

Do you have any suggestions?

I appreciate any assistance,
Dyana

 

[LSTAN]

Hi dyana

What exactly is the problem or is there any error/s?

Does the icon face appear?

Does it do something when you click on it?

By the way,
FaceId=1142 'is an right arrow icon

I try the FaceId=1142 and it show a right arrow. It work on both Excel97 and Excel2000.

If you got nothing when clicking on the icon, then you may be missing the OnAction event.

rgrds
LSTAN

 

[dyana]

Below is the code that I tried. I utilized the same code with the only change being the faceid from "17" to "1142".



Sub FirstYearButton2()
'
' FirstYearButton2 Macro
' Macro recorded 4/1/2002 by dparsons
'
Dim newBar As CommandBar, con As CommandBarControl

Set newBar = CommandBars.Add(Name:="YearOne", _
Position:=msoBarTop, Temporary:=True)

newBar.Visible = True

Set con = newBar.Controls.Add(Type:=msoControlButton)

With con
.FaceId = 1142
.OnAction = "ShowFirstYear"
End With

End Sub


The error I got was "Run-time error 5" and "Invalid procedure call or argument".

Thanks

 

[LSTAN]

Hi dyana

One thing for sure, there is nothing wrong with your code. I have try out your code, it works fine, and I try to generate the Run-time Error 5 that you have mentioned.

This is the 2 scenerio that I tired
[tt]
1) With the toolbar already visible and I try to run the procedure again. It will generate the error.

2) This one is a bit lengthy to explain. When you run the code the first time and it generate an error and after you have debugged and do the necessary amendment. And you try to run the procedure again ( the toolbar is not visible )you will get the run-time error. The reason is because the code already generated the toolbar and when re-run the code will try to generate another toolbar with the same name. So you have to manually delete the toolbar by View -> Toolbars -> Customize.[/tt]

If the problem still persist, let me know the line that is generating this error.

I might not replied immediately ( will try to reply within two days ) as I am sitting for my Engineering Degree exams this week.

Good luck and regards

LSTAN

 

[dyana]

I wish you the best with your engineering exams.



I did previously try what you recommended, the deleting the icon and rerunning but it still did not work.

The part of the code with the problem is:

Set newBar = CommandBars.Add(Name:="YearOne", _
Position:=msoBarTop, Temporary:=True)


I really appreciate all of your help. I have not problem waiting until it is convenient for you to respond.

Again good luck on the exams.

Dyana

 

[LSTAN]

Hi dyana

Thank you.

Find it very strange why you are getting this run-time error. Anyway, how about add a sequence to check whether a existing YearOne toolbar exist. Here is the amended code;

Sub FirstYearButton2()
'
' FirstYearButton2 Macro
' Macro recorded 4/1/2002 by dparsons
'
Dim newBar As CommandBar, con As CommandBarControl
Dim ctrl
'=============================================
'this procedure loop through the application commandbars
'and check whether any toolbar with name "YearOne" exist
'if yes delete it
'
'
For Each ctrl In Application.CommandBars
If ctrl.Name = "YearOne" Then
ctrl.Delete
Exit For
End If
Next
'===============================================
Set newBar = CommandBars.Add(Name:="YearOne", _
Position:=msoBarTop, Temporary:=True)
newBar.Visible = True
Set con = newBar.Controls.Add(Type:=msoControlButton)
With con
.FaceId = 133 '<---I also change this
.OnAction = &quot;ShowFirstYear&quot;
End With
End Sub

Give this code a try. Let me know if the problem still on.

rgrds
LSTAN