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?

 

[Dan_W]

I get the impression that the OP wants a one-time macro that will insert images into controls in Designer mode.

Artik

Thank you so much.

if I have jpg picture and upload picture to commandbutton will keep the picture when run the form will show the picture as I uploaded, but in png picture when upload to commandbutton will not show the picture has ever uploaded when run the form after clos the form agian . based on your suggestion wen show browser and select picture to upload and when close the form and open again will not show uploaded picture . in png picture I have to upload picture for every time run the form .
I would the same way like JPG picture upload from first time then will show when every time run the form without I upload every time run the form.

Yes. That is also possible. I'll write some code to do it.

 

[Dan_W]

First, you need to add a reference to the VBA Extensibility Library. First click Tools -> References:

Then - you may have to scroll down - select Microsoft Visual Basic for Applications Extensibility:

I think I might be missing a step here, but once you have done that, put the following code into a standard module. You can call this subroutine similar to the following:

Sub Test()
  LoadImageToControl "C:\code\smiley.png", "UserForm1", "CommandButton1"
End Sub

The fist argument is to the image file - this can be PNG, JPG, BMP, GIF, etc. The second argument is the name of the userform where the control is, and the third is the name of the control. If any of these are incorrect, the code below will tell you about it! When you run the routine, go have a look at the userform and see if it worked. Save the workbook, and then it will be there when you open it next time.

Alternatively, to do it manually, you can just open the file in MS Paint, select and copy the image, and then literally go and ctrl-v paste it into the picture property of the control. Here is a demo of @sancarn doing it:

Public Sub LoadImageToControl(ByVal Filename As String, ByVal VBCName As String, ByVal ControlName As String)
  
  If Len(Dir(Filename)) = 0 Then
    MsgBox "The filename - " & Filename & " - does not exist."
    Exit Sub
  End If
  
  Dim TargetObject As MSForms.Control, VBP As VBProject, VBC As VBComponent, DesignTool As Object
  Set VBP = Application.VBE.ActiveVBProject
  
  On Error Resume Next
  Set VBC = Application.VBE.ActiveVBProject.VBComponents(VBCName)
  On Error GoTo 0
  
  If VBC Is Nothing Then
    MsgBox "The userform - " & VBCName & " - does not exist."
    Exit Sub
  End If
  
  If VBC.HasOpenDesigner Then
    Set DesignTool = VBC.Designer
    On Error Resume Next
    If Len(ControlName) Then
      Set TargetObject = DesignTool.Controls(ControlName)
    End If
    On Error GoTo 0
    If TargetObject Is Nothing Then
      MsgBox "The control - " & ControlName & " - does not exist."
      Exit Sub
    End If
    Set TargetObject.Picture = LoadPicture("")
    If Len(Filename) Then
      If Not TargetObject Is Nothing Then
        With CreateObject("WIA.ImageFile")
          .LoadFile Filename
          TargetObject.Picture = .FileData.Picture
        End With
      End If
    End If
  End If
End Sub

Hope that helps.

 

[MKLAQ]

it will show compile error sub or function not defined in this word "LoadImageToControl "

 

[Dan_W]

So... LoadImageToControl is the name of the subroutine of the code that I wrote for you and that I posted immediately above. I'll post it again for you here. Copy and paste the code I wrote for you into the workbook that you want to run it in.

Public Sub LoadImageToControl(ByVal Filename As String, ByVal VBCName As String, ByVal ControlName As String)
  
  If Len(Dir(Filename)) = 0 Then
    MsgBox "The filename - " & Filename & " - does not exist."
    Exit Sub
  End If
  
  Dim TargetObject As MSForms.Control, VBP As VBProject, VBC As VBComponent, DesignTool As Object
  Set VBP = Application.VBE.ActiveVBProject
  
  On Error Resume Next
  Set VBC = Application.VBE.ActiveVBProject.VBComponents(VBCName)
  On Error GoTo 0
  
  If VBC Is Nothing Then
    MsgBox "The userform - " & VBCName & " - does not exist."
    Exit Sub
  End If
  
  If VBC.HasOpenDesigner Then
    Set DesignTool = VBC.Designer
    On Error Resume Next
    If Len(ControlName) Then
      Set TargetObject = DesignTool.Controls(ControlName)
    End If
    On Error GoTo 0
    If TargetObject Is Nothing Then
      MsgBox "The control - " & ControlName & " - does not exist."
      Exit Sub
    End If
    Set TargetObject.Picture = LoadPicture("")
    If Len(Filename) Then
      If Not TargetObject Is Nothing Then
        With CreateObject("WIA.ImageFile")
          .LoadFile Filename
          TargetObject.Picture = .FileData.Picture
        End With
      End If
    End If
  End If
End Sub

 

[MKLAQ]

I suppose to put in form module ,right?
I'm not sure why the error continues showing!

 

[Jack George]

I suppose to put in form module ,right?
I'm not sure why the error continues showing!

No, the code should be placed in a standard module. Just click on VBA IDE menu Insert -> Module, and paste the code in, run the test code and see what happens. (It CAN be placed and used in a class module, though that's another story)

 

[Jack George]

Using the fantastic code from #22 by Dan_W.

Before adding image to UserForm control:

After Running the "Test" sub in Module2:

Code in UserForm1 module:

Option Explicit

Code in Module1 module:

Option Explicit

Public Sub LoadImageToControl(ByVal Filename As String, ByVal VBCName As String, ByVal ControlName As String)
 
    If Len(Dir(Filename)) = 0 Then
        MsgBox "The filename - " & Filename & " - does not exist."
        Exit Sub
    End If
 
    Dim TargetObject As MSForms.Control, VBP As VBProject, VBC As VBComponent, DesignTool As Object
    Set VBP = Application.VBE.ActiveVBProject
 
    On Error Resume Next
    Set VBC = Application.VBE.ActiveVBProject.VBComponents(VBCName)
    On Error GoTo 0
 
    If VBC Is Nothing Then
        MsgBox "The userform - " & VBCName & " - does not exist."
        Exit Sub
    End If
 
    If VBC.HasOpenDesigner Then
        Set DesignTool = VBC.Designer
        On Error Resume Next
        If Len(ControlName) Then
            Set TargetObject = DesignTool.Controls(ControlName)
        End If
        On Error GoTo 0
        If TargetObject Is Nothing Then
            MsgBox "The control - " & ControlName & " - does not exist."
            Exit Sub
        End If
        Set TargetObject.Picture = LoadPicture("")
        If Len(Filename) Then
            If Not TargetObject Is Nothing Then
                With CreateObject("WIA.ImageFile")
                    .LoadFile Filename
                    TargetObject.Picture = .FileData.Picture
                End With
            End If
        End If
    End If
End Sub

Code in Module2 module:

Option Explicit

Sub Test()
    LoadImageToControl "D:\qrcodes\QRCode_0002838.emf", "UserForm1", "CommandButton1"
End Sub

If the number of controls to add icons to is less than 20, I would suggest you to add them manually. And the format of the icon file (jpg, ico, bmp, png, gif, wmf...) is critical for the final visual result, and the emf (Enhanced Meta File) will give better results than others if your icon can be drawn geometrically (Vector vs Raster).

 

[Dan_W]

Thatnk you very much for your help. Hopefully, OP has managed to get the code wo

If the number of controls to add icons to is less than 20,

I think this makes a lot of sense. There usually comes a point when it just makes more sense to do it manually.
Personally, though, I tend not to add images to controls at design time. I usually find that the 'heavier' the userform is with controls and images and code, the more likely it is that something will go horribly wrong and the userform will be corrupted... which happens to me a lot!

 

[MKLAQ]

No, the code should be placed in a standard module

in this case will show error message" Programmatic Access To Visual Basic Project Is Not Trusted"

  Set VBP = Application.VBE.ActiveVBProject

 

[Dan_W]

Ah yes, in order to programmatically do anything in the VBA IDE, you need to give yourself access to do so by changing a setting in Office:

To enable or disable access to Visual Basic projects​

1. Click the File tab.
2. Click Options.
3. Click Trust Center, and then click Trust Center Settings.
4. In the Trust Center, click Macro Settings.
5. Check or uncheck Trust access to the VBA project object model to enable or disable access to Visual Basic Projects.
6. Click OK

You may need to restart Execel/Word/whatever after doing this. You should read this too: VBA access to create/open a VSTO system project