+1 vote
in VBA by Beginner (24 points)
On a worksheet, there is a range which I would like to display on the userform as an image.

How can I achieve that?

1 Answer

+2 votes
by Skilled (401 points)
selected by
Best answer

Insert a userform >> Create CommandButton1 (Name = cmdClose) >> Create Image1 (resize to fit your range)

then in Standard Module put this code

Sub ShowForm()
End Sub

Function ExportRangeAsPictureFile(rExport As Range, sExport As String) As Boolean
    Dim dh As Double, dw As Double

    dh = 1: dw = 1

    On Error Resume Next
        Kill sExport
    On Error GoTo 0

    If rExport Is Nothing Then GoTo Exit_Func
    rExport.CopyPicture appearance:=xlScreen, Format:=xlBitmap

    With ActiveSheet.ChartObjects.Add(Left:=rExport.Left, Top:=rExport.Top, Width:=rExport.Width + dw, Height:=rExport.Height + dh)
        With .Chart
            Do Until .Pictures.Count = 1
                DoEvents: .Paste
            .ChartArea.Format.Line.Visible = msoFalse
            .Export sExport
            ExportRangeAsPictureFile = True
        End With
    End With

End Function

And in UserForm Module put this code

Private Sub UserForm_Initialize()
    Dim oSelection      As Object
    Dim rSelection      As Range
    Dim bExport         As Boolean
    Dim sPathName       As String
    Dim sFileName       As String
    Dim sExportName     As String

    sPathName = Environ("tmp")
    sFileName = "Temp.gif"
    sExportName = sPathName & Application.PathSeparator & sFileName
    Set oSelection = Sheet1.Range("A1:B6")

    If TypeName(oSelection) = "Range" Then
        Set rSelection = oSelection
        bExport = ExportRangeAsPictureFile(rSelection, sExportName)
        If bExport Then Me.Image1.Picture = LoadPicture(sExportName)
        Me.Hide: Exit Sub
    End If
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub


by Beginner (24 points)
Works like charm. Thank you very much.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and/or answers. Users that ask great questions, may be given complimentary gift cards or training material.

Getting Started

So, why don't you join us? It really is a neat way to reward the most dedicated members in our VBA and Python community.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.