Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

VBA code For User Form

Copper Contributor

Sub ChangeShape(ShapeName As String)
Dim CurrentShape As Shape
Dim FName As String
FName = ThisWorkbook.Path & "\tempshape.gif" ' Change the temporary file name

' Set the current shape
Set CurrentShape = ThisWorkbook.Sheets("Animals").Shapes(ShapeName)

' Copy the shape
CurrentShape.Copy

' Add a new picture to the sheet and paste the copied shape
With ThisWorkbook.Sheets("Animals").Pictures.Paste
' Export the pasted picture as a GIF
.Export Filename:=FName, FilterName:="GIF"
End With

' Load the exported picture to the TableImage on the AnimalTimeData Userform
AnimalTimeData.TableImage.Picture = LoadPicture(FName)
End Sub

                  Can anyone help me with this code. The property is not supported for shapes. I want to import a shape in an image of my VBA UserForm in excel. The shape is in the excel sheet.  The shape must be dynamically update therefore the sub ChangeShape.

 

3 Replies

@HennieUnique 

It seems like you are trying to copy a shape from a worksheet, export it as a GIF file, and then load that GIF into an Image control on a UserForm. However, the Picture property is not supported for shapes, and exporting a shape directly as an image might be a bit tricky.

One workaround is to capture a screenshot of the shape and save it as an image.

Here is an example of how you might modify your code:

Vba code (is untested):

Sub ChangeShape(ShapeName As String)
    Dim CurrentShape As Shape
    Dim FName As String
    Dim UF As AnimalTimeData
    
    ' Set the current shape
    Set CurrentShape = ThisWorkbook.Sheets("Animals").Shapes(ShapeName)
    
    ' Set the userform
    Set UF = AnimalTimeData
    
    ' Capture a screenshot of the shape
    CurrentShape.CopyPicture
    
    ' Add a new picture to the sheet and paste the copied shape
    With ThisWorkbook.Sheets("Animals").Pictures.Paste
        ' Export the pasted picture as a GIF
        FName = ThisWorkbook.Path & "\tempshape.gif" ' Change the temporary file name
        .ShapeRange.ExportAsFixedFormat Type:=xlTypeGIF, Filename:=FName
    End With

    ' Load the exported picture to the Image control on the AnimalTimeData UserForm
    UF.TableImage.Picture = LoadPicture(FName)
End Sub

 

In this modification, the CopyPicture method is used to capture the shape as it appears on the sheet, and then it is saved as a GIF file. The ExportAsFixedFormat method is used to save the picture as a GIF file.

Make sure that you have an Image control named TableImage on your AnimalTimeData UserForm.

Please note that capturing the shape as it appears on the sheet might not be as precise as exporting the shape directly. If you have complex shapes with gradients or special effects, the result may not be perfect.

Test this code in your specific environment, and adjust it as needed for your application.

AI was partially deployed to support the text.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Thank you NikolinoDE .
The property Pictures.Paste is not supported.
I tried in another code as well. Any other suggestions. The rest of the code is OK.
Hope you can think about an alternative.

I also have the following code working fast and well in the same UserForm. It displays a dynamic graph from the same sheet in the GraphImage of the UserForm: Maybe you can compare and think about alternative coding to export the shape as an picture and then to add to the Userform:
.......................................................................................................................
Sub ChangeChart(ChartName As String)
Dim CurrentChart As Chart
Dim FName As String
FName = ThisWorkbook.Path & "\temp.gif"

' Set the current chart
Set CurrentChart = ThisWorkbook.Sheets("Animals").ChartObjects(ChartName).Chart

' Export the chart as a GIF
CurrentChart.Export Filename:=FName, FilterName:="GIF"

' Load the exported picture to the GraphImage on AnimalTimeData UserForm
AnimalTimeData.GraphImage.Picture = LoadPicture(FName)

End Sub
......................................................................................................................

@HennieUnique 

Alternative you can use the Copy method to copy the shape to the Clipboard and then use a workaround to save it as an image. Below is an updated version of your code:

vba Code (is untested):

Sub ChangeShape(ShapeName As String)
    Dim CurrentShape As Shape
    Dim FName As String
    Dim UF As AnimalTimeData
    
    ' Set the current shape
    Set CurrentShape = ThisWorkbook.Sheets("Animals").Shapes(ShapeName)
    
    ' Set the userform
    Set UF = AnimalTimeData
    
    ' Copy the shape to the Clipboard
    CurrentShape.Copy
    
    ' Create a new temporary chart to paste the shape
    With ThisWorkbook.Sheets.Add
        .Shapes.AddChart2(251, xlColumnClustered).Select
        .Paste
    End With
    
    ' Export the pasted chart as a GIF
    FName = ThisWorkbook.Path & "\tempshape.gif" ' Change the temporary file name
    ActiveChart.Export Filename:=FName, FilterName:="GIF"
    
    ' Delete the temporary sheet
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True

    ' Load the exported picture to the Image control on the AnimalTimeData UserForm
    UF.TableImage.Picture = LoadPicture(FName)
End Sub

In this code:

  1. The Copy method is used to copy the shape to the Clipboard.
  2. A temporary chart is created on a new sheet, and the shape is pasted onto the chart.
  3. The chart is exported as a GIF file.
  4. The temporary sheet is deleted.

Please replace "xlColumnClustered" with the appropriate chart type that suits your needs. Test this code and see if it meets your requirements.