Forum Discussion

HennieUnique's avatar
HennieUnique
Copper Contributor
Nov 24, 2023

VBA code For User Form

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 Cur...
developer
excel
office 365
HennieUnique's avatar
HennieUnique
Copper Contributor
Nov 24, 2023
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
......................................................................................................................
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 25, 2023

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.

Resources