can add icons as buttons without add commandbutton on userform

[MKLAQ]

Hi,
I'm not sure what I ask maybe could in excel.
as known when add icon into commandbutton on form will not show the caption clearly after upload icon . I have to resize the button then will be much big to show caption. my goal I will design the button by PHOTHOSHOP and I will upload to excel.
my question is: can I add the icon as picture on form without add commandbutton like inside sheet will use some shapes (rectangular, oval) and link it with macro?

 

[RoryA]

You could add an image control and use that, but you can't add shapes.

 

[MKLAQ]

You could add an image control and use that

but how add png image to commandbutton
I have the code about png but shows error when link with commandbutton1
like this

Private Sub UserForm_Activate()
Dim vntFilename As Variant
    
    vntFilename = Application.GetOpenFilename("Images (*.png),*.png")
    If vntFilename = "False" Then Exit Sub
    
    CommandButton1.Caption = LoadImage(vntFilename)
End Sub

 

[RoryA]

use the picture property not caption

 

[MKLAQ]

unfortunately doesn't show picture and doesn't show error!

Private Sub UserForm_Activate()
Dim vntFilename As Variant
    
    vntFilename = Application.GetOpenFilename("Images (*.png),*.png")
    If vntFilename = "False" Then Exit Sub
    
    CommandButton1.Picture = LoadImage("C:\Users\MK\Pictures\qw.png")
End Sub

 

[RoryA]

Oh, you can’t use png files that way. Either use a different format, or read this: function load transparent PNG picture into userform

 

[MKLAQ]

I see label could use it so I suppose commandbutton should use it too.
but doesn't work

Private Sub UserForm_Activate()
Dim vntFilename As Variant
    
    vntFilename = Application.GetOpenFilename("Images (*.png),*.png")
    If vntFilename = "False" Then Exit Sub
    
    Me.CommandButton1.Picture = LoadImage("C:\Users\MK\Pictures\qw.png,Me.CommandButton1")
End Sub

 

[RoryA]

Again, you cannot use a png format like that.

 

[Dan_W]

Hi,
I'm not sure what I ask maybe could in excel.
as known when add icon into commandbutton on form will not show the caption clearly after upload icon . I have to resize the button then will be much big to show caption. my goal I will design the button by PHOTHOSHOP and I will upload to excel.
my question is: can I add the icon as picture on form without add commandbutton like inside sheet will use some shapes (rectangular, oval) and link it with macro?

As @RoryA said, you need to use the Picture property (and not caption). And as VBA does not natively handle PNG, you will need to add the either the function referred above, or this one (which is simpler and easier to use, in my opinion):

Function LoadImage(ByVal Filename As String) As StdPicture
  With CreateObject("WIA.ImageFile")
    .LoadFile Filename
    Set LoadImage = .FileData.Picture
  End With
End Function

So you final code would look something like:

Private Sub UserForm_Activate()
  Dim vntFilename As Variant 
  vntFilename = Application.GetOpenFilename("Images (*.png),*.png")
  If vntFilename = "False" Then Exit Sub
  Me.CommandButton1.Picture = LoadImage(vntFilename)
End Sub

Function LoadImage(ByVal Filename As String) As StdPicture
  With CreateObject("WIA.ImageFile")
    .LoadFile Filename
    Set LoadImage = .FileData.Picture
  End With
End Function

More about dealing with PNG files here: Working with images in VBA - Displaying PNG files

 

[MKLAQ]

Thanks Dan ,

I see this is not useful way ,because it doesn't save image after close form when load image to commandbutton1 . I have to load image every time run the form . this is disappointed, sorry !