Excel VBA: Load a Shape into a UserForm Image Control
Question details
The user needs to display an Excel Shape in a VBA UserForm Image control.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro must take a Shape from the Animals worksheet, convert it to an image file, and show it in the TableImage control on a UserForm.
- Observed behavior
- A Shape object cannot be assigned directly to the Picture property of a VBA UserForm Image control.
Confirm that the worksheet is named Animals and that the UserForm contains an Image control named TableImage. Also verify that the workbook folder is writable so the temporary PNG file can be created.
Export the Excel Shape as a PNG and load it into the UserForm
Use this approach when a Shape must be displayed in a VBA UserForm Image control.
A UserForm Image control expects a picture object or image file, not an Excel Shape object. Copy the Shape as a picture, paste it into a temporary chart, export the chart as PNG, and then load the file with LoadPicture.
Press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste this code: Sub ChangeShape(ShapeName As String) Dim ws As Worksheet Dim currentShape As Shape Dim tempChart As Chart Dim fileName As String Set ws = ThisWorkbook.Worksheets("Animals") Set currentShape = ws.Shapes(ShapeName) fileName = ThisWorkbook.Path & Application.PathSeparator & "tempshape.png" currentShape.CopyPicture Appearance:=xlScreen, Format:=xlPicture Set tempChart = ws.ChartObjects.Add(0, 0, currentShape.Width, currentShape.Height).Chart tempChart.Paste tempChart.Export Filename:=fileName, FilterName:="PNG" tempChart.Parent.Delete AnimalTimeData.TableImage.Picture = LoadPicture(fileName) End Sub
In the Project Explorer, open the UserForm, select its Image control, and confirm that the Properties window shows (Name) as TableImage. Change the code reference if the control has a different name.
In the Immediate window or another procedure, run ChangeShape with the exact Shape name, for example: ChangeShape "Picture 1". The macro creates tempshape.png in the workbook folder and loads it into TableImage.
Open the UserForm and confirm that the image appears. If PNG export is unavailable in the Excel version, change the export filter to a supported format such as JPG and update the temporary file extension.
Use WPS Spreadsheets to edit and test VBA-enabled workbooks
WPS Spreadsheets provides a familiar spreadsheet interface for opening, editing, and testing compatible VBA-enabled workbooks. Use the macro editor to review the Shape-to-image conversion code, then save the workbook in its original format for continued use.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and select the VBA-enabled .xlsm workbook from the Open dialog.
- 2. Open the macro editor: Use the Tools or Developer menu to open the Visual Basic or macro editor, depending on the WPS version and platform.
- 3. Review the conversion procedure: Locate the module containing ChangeShape and verify the Animals worksheet, TableImage control, temporary file path, and PNG export settings.
- 4. Run and verify the macro: Run the macro from the editor or the macro dialog, then open the UserForm and confirm that the exported Shape appears in the Image control.
Frequently Asked Questions
Why can’t I assign an Excel Shape directly to a UserForm Image control?
A Shape is an Excel drawing object, while the UserForm Image control expects a picture. Convert the Shape to a picture file or a compatible picture object before assigning it to the control.
Where does the temporary exported image get saved?
The example saves tempshape.png in ThisWorkbook.Path, which is the folder containing the workbook. Ensure the workbook has been saved and that the folder allows file creation.
What should I do if TableImage is not recognized?
Open the UserForm in the Visual Basic Editor, select the Image control, and set its (Name) property to TableImage. Alternatively, replace TableImage in the code with the control’s actual name.
Can I export the Shape as a GIF instead of PNG?
PNG or JPG is recommended because chart export support varies by Excel version. ExportAsFixedFormat generally creates PDF or other fixed-layout output rather than a GIF image.
How do I remove the temporary image after loading it?
Load the picture into the Image control first, then use the VBA Kill statement with the full file path after confirming that the control has loaded the image and no longer needs the source file.
