Excel VBA Macro – Create Print Preview Within UserForm in Image Control Box

5 viewsexceluserformvba
0

the below code should show a preview image within Image Control box named Image1 on the same userform, but its not, there’s a hold up at Image1.Picture = LoadPicture(Environ("TEMP") & "PrintPreview.png"), I checked it the Temp directory, its saving the PrintPreview.png file as PrintPreview.png.pdf so it cant load it to the Image control box, any way to resolve?

Private Sub CommandButton8_Click()

      Application.EnableEvents = False
    Me.Hide
    
    ' Create a temporary worksheet to hold the print area
    Dim tempSheet As Worksheet
    Set tempSheet = Sheets.Add
    
    ' Copy the desired range to the temporary sheet
    Worksheets("InputSheet").Range("A1:I31").Copy tempSheet.Range("A1")
    
    ' Set print area for the temporary sheet
    tempSheet.PageSetup.printArea = tempSheet.UsedRange.Address
    
    ' Create a chart to capture a screenshot of the print preview
    Dim tempChart As ChartObject
    Set tempChart = tempSheet.ChartObjects.Add(0, 0, tempSheet.UsedRange.Width, tempSheet.UsedRange.Height)
    
    ' Copy the temporary sheet to the chart
    tempChart.Chart.Paste
    
    ' Export the chart as an image
    tempChart.Chart.Export Environ("TEMP") & "PrintPreview.png", "PNG"
    
    ' Delete the temporary sheet and chart
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
    
    ' Load the captured image into the Image control
    Image1.Picture = LoadPicture(Environ("TEMP") & "PrintPreview.png")
    
    ' Show the UserForm again
    Me.Show
    Application.EnableEvents = True

End Sub

When I click the button I’m expecting a PrintPreview.png image to apprear in image control box "Image1" on the same userform