How to get a range as a picture on a userform

[ideasfactory]

Hi

I have a range that I need to show on a userform I DO NOT want to use a listbox. I want the range that ends up on the userform to look exactly like the original range on the sheet.

Does anyone know the vba to do this?

Is this achieved with a macro and the copy and paste linked feature?

Example VBA would be great.

Thanks

 

[John_w]

See https://www.mrexcel.com/forum/excel-questions/566836-display-range-cells-worksheet-userform.html

 

[Jon Peltier]

This approach will put a picture of the selected range into a UserForm (i.e., select a range, then run the procedure that loads the UserForm). It's simpler than the Windows API approach linked to in the other answer.

What it does is copy the selected range as a bitmap picture, paste it into a temporary chart that contains no data, exports the chart as a GIF image, then loads the GIF into the image control on the UserForm.

You can make the program more elaborate: you can resize the image control to fit the picture of the range, you can resize the UserForm to accommodate the image control, you can use an input box to ask the user what range to put into the image control, etc.

Create a UserForm, and name it F_PicRangeIntoImage.
Add an image control, and name it imgRange.
Make the image control and userform large enough for the selected range.

Put this code into the UserForm, along with all other code needed for its functionality:

Private Sub UserForm_Initialize()
  Dim sExportName As String
  Dim sPathName As String
  Dim sFileName As String
  Dim rSelection As Range
  Dim oSelection As Object
  
  sPathName = Environ("tmp") ' temp directory
  sFileName = "temp.gif" ' can't handle png
  sExportName = sPathName & Application.PathSeparator & sFileName
  
  Set oSelection = Selection
  If TypeName(oSelection) = "Range" Then
    ' no shape selected
    ' make new shape to hold selected range
    Set rSelection = oSelection

    bExport = ExportRangeAsPictureFile(rSelection, sExportName)
    If Not bExport Then btnClose_Click
  
    Me.imgRange.Picture = LoadPicture(sExportName)
  Else
    Me.Hide
    Exit Sub
  End If
  
End Sub

Put this code into a regular module.

Sub LoadSelectionIntoImgOnUserForm()
  Dim frm As F_PicRangeIntoImage
  
  Set frm = F_PicRangeIntoImage
  With frm
    .Show
  End With
  Unload frm
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)

    DoEvents
    With .Chart
      Do Until .Pictures.Count = 1
        DoEvents
        .Paste
      Loop

      .ChartArea.Format.Line.Visible = msoFalse

      .Export sExport
      
      ExportRangeAsPictureFile = True ' it worked
    End With

    .Delete
  End With

Exit_Func:

End Function

 

[ideasfactory]

Hi,

Thanks, I followed the instructions above, the Userform loads but the code stops with error Sub or Function not defined for:

If Not bExport Then btnClose_Click

Is it possible to provide code that works without having to amend.

I am not sure if I need to change anything else for example do I need to replace tmp with and actual path? this is why I need working code as I am not a programmer but can follow logic.

sPathName = Environ("tmp") ' temp directory

Thanks

 

[ideasfactory]

Hi,

In addition to the above response, the range I have is fixed Sheet4 A1:F100.

Also is it possible to add scroll on an image control or embed image control, as the range is longer than the Userform i.e. 100 rows?

 

[My Aswer Is This]

Why not just use a muticolumn listbox?
Then you can load all your values in the listbox

 

[ideasfactory]

Why not just use a muticolumn listbox?
Then you can load all your values in the listbox

Hi,

I tried that but unfortunately I cannot use multi column list box due to the structure of the data in the range got merged cells etc. Is there a way to have an image control on a userform that I can scroll vertically somehow, may be embed image control inside something that can scroll vertically?

Thanks

Paul

 

[Jon Peltier]

I left out something in my UserForm.
Add a command button, name it btnClose.

Private Sub btnClose_Click()
  ' whatever code you need when the UserForm is closed
  ' blah blah
  ' endinf with
  Unload Me
End Sub

My bad. I was embellishing my example after I wrote the description but before I posted the code.

Environ("tmp") is an environment function that simply returns the path to the temp directory. I don't think you need to keep these fleeting images for longer than you need to save them and load them into the dialog.

 

[Jon Peltier]

Well, this is getting intricate.

You can't put a scrollbar on an image control, but you can put a scrollbar on a frame control, and put the image in the frame. You need to make the scrollheight of the frame as tall as the image is, plus whatever margin you want within the frame above and below the image. And you can make this as dynamic as you have the bookkeeping skills to handle.

 

[Jon Peltier]

A multicolumn listbox would certainly be a more workable approach. You may have to work with number formats to make some of the values more legible, for example, make sure percentages and currency look like percentages and currency.

You say that the structure of the worksheet won't allow this, and you mention merged cells. Well, this might be the excuse you need to clean up the worksheet. Merged cells are the devil's spawn, and cause more problems than they solve. They're okay on the final display worksheet, but they make working with data problematic and potentially error-prone, and the errors are hidden by the apparent structure. Also complications like excessive formatting can make the worsheet look pretty but cause confusion and complexity.