VBA Excel CommandButtons for Macros... 1

[plopez]

Hi, I need to put an image over a CommandButton using the Code.
Thanks 4 help me :=)

 

[VulcanJedi]

1: If your command button is IN a SHEET I don't think you can embed a picture on it.
--A creative solution would be....assign a macro to the picture itself, basically maintaining the same functionality.
2: IF the button is on a USERFORM: Click properties in the VBE. There is a properties called picture and it will allow you to browse to a directory where your image file is.

Rgds.
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[plopez]

Hi Tranpkp, I forgot to say that my button is a CommandButton in the MenuBars.

Sub AddMenu()
With Application.CommandBars.Add("MyMenu", , True, True)
.Visible = True
With .Controls
With .Add(msoControlButton)
.Caption = "Button1"
.FaceID = 59
End With
End With
End With
End Sub

The only way you have to put an image is the .FaceID property that only take the value of a number that contains an icon inside the DLL´s system files.

I need to create my own images on the buttons and I dont know the to do it.
It is a little more clear now?

Thank U 4 Ur answer.....rgds
plopez

 

[plopez]

Hi , I m trying to put my own images on the CommandButtons using code,not the Office images.

My code is like this:

Sub AddMenu()
With Application.CommandBars.Add("MyMenu", , True, True)
.Visible = True
With .Controls
With .Add(msoControlButton)
.Caption = "Button1"
.FaceID = 59
End With
End With
End With
End Sub

The only way you have to put an image is the .FaceID property that only take the value of a number that contains an icon inside the DLL´s system files.

I need to create my own images on the buttons and I dont know the to do it.

Thank U 4 Ur answer.....rgds
plopez

 

[rmikesmith]

plopez,

The only way I've found to do this is to embed your icon(s) into a worksheet (you can use Insert/Picture/From File...) then have your code

1) Use the Copy method of the Shapes object
2) Use the PasteFace method of the CommandBarButton object

Here is a modified version of your procedure that I've tested:

Sub AddMenu()
Dim btnNew As CommandBarButton

  With Application.CommandBars.Add("MyMenu", , True, True)
    .Visible = True
    Set btnNew = .Controls.Add(msoControlButton)
    With btnNew
      .Style = msoButtonIconAndCaption
      .Caption = "Button1"
      ActiveSheet.Shapes("Picture_1").Copy
      .PasteFace
     End With
  End With
  
End Sub

Note: After inserting your icon, you can give a name of your choosing same as other objects. You can place the picture in some out of the way location on a worksheet used for other purposes or place on its own worksheet and hide that worksheet. You will, of course, need to change ActiveSheet to a specific worksheet reference.

HTH
Mike

 

[plopez]

rmikesmith,

Your solution to my problem worked so good!

I apreciate your help,thank you very much. :-D

 

[RobBroekhuis]

Hey, plopez - how about a star for mike?