Display Excel Workbook on a Userform

Question

Objective: As the title suggest, how do we display the workbook on the userform without using any third party controls.

By Display, I mean show it on a userform where a user can select a worksheet and view the contents of that worksheet.

This post is an attempt to self answer the question.

Answer 1

The method that I am going to demonstrate below will not use any Third Party Control. In fact it will display the worksheet in an image control. This obviously means that you cannot interact with the worksheet. It is only for displaying the data from the worksheet.

Basic Setup

Create a userform and place the controls as shown below. I have included a sample file at the end of the post. Feel free to tinker with it and make it better. Also feel free to resize the userform to suit your needs.

Code

Option Explicit

Dim wb As Workbook
Dim ws As Worksheet

Private Sub CommandButton1_Click()
    Dim Ret As Variant

    '~~> Browse the excel file
    Ret = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")

    If Ret = False Then Exit Sub Else TextBox1.Text = Ret

    ComboBox1.Clear

    '~~> Open the workbook and hide it
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(TextBox1.Text)
    ActiveWindow.Visible = False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True

    '~~> Add the worksheet names to the combobox
    For Each ws In wb.Worksheets
        ComboBox1.AddItem ws.Name
    Next ws

    '~~> Set the min and max for the scrollbars
    SBVert.Min = 1
    SBVert.Max = wb.Sheets(1).Columns.Count

    SBHorz.Min = 1
    SBHorz.Max = wb.Sheets(1).Rows.Count
End Sub

'~~> Trap Scrollbar Changes
Private Sub SBHorz_Change()
    GetRangeToDisplay SBVert.Value, SBHorz.Value
    DoEvents
End Sub

'~~> Trap Scrollbar Changes
Private Sub SBVert_Change()
    GetRangeToDisplay SBVert.Value, SBHorz.Value
    DoEvents
End Sub

'~~> On exit close the ghidden file
Private Sub UserForm_Terminate()
    If Not wb Is Nothing Then wb.Close (False)
End Sub

'~~> User selects the worksheet
Private Sub ComboBox1_Click()
    If ComboBox1.ListIndex = -1 Then Exit Sub

    Set ws = wb.Sheets(ComboBox1.Value)

    GetRangeToDisplay 1, 1
End Sub

'~~> Get the address of the range to display
Sub GetRangeToDisplay(fr As Long, fc As Long)
    If ws Is Nothing Then Exit Sub

    Dim RowHeight As Long, ColWidth As Long
    Dim tmpWidth As Long, tmpRow As Long
    Dim rngToDisplay As Range
    Dim displayedLastRow As Long, displayedLastCol As Long

    '~~> Max width/height of the range to display
    '~~> Change this as applicable
    '~~> Choose these numbers carefully as they will
    '~~> impact how the image looks like in the image control
    Const MaxWidthToDisplay As Integer = 255
    Const MaxHeightToDisplay As Integer = 409

    displayedLastRow = fr: displayedLastCol = fc

    Do
        displayedLastRow = displayedLastRow + 1
        displayedLastCol = displayedLastCol + 1

        tmpWidth = ColWidth + ws.Columns(displayedLastCol).ColumnWidth
        tmpRow = RowHeight + ws.Rows(displayedLastRow).RowHeight

        If Not tmpWidth > MaxWidthToDisplay Then _
        ColWidth = ColWidth + ws.Columns(displayedLastCol).ColumnWidth

        If Not tmpRow > MaxHeightToDisplay Then _
        RowHeight = RowHeight + ws.Rows(displayedLastRow).RowHeight

        If tmpWidth > MaxWidthToDisplay And _
        tmpRow > MaxHeightToDisplay Then Exit Do
    Loop

    Set rngToDisplay = ws.Range(ws.Cells(fr, fc), _
                       ws.Cells(displayedLastCol, displayedLastCol))

    DisplayRange rngToDisplay
End Sub

'~~> Function to export range as an image and then load
'~~> that image in the image control
Sub DisplayRange(r As Range)
    Dim wsChart As Worksheet
    Dim fname As String

    '~~> This is the temp sheet where the temp chart will be created
    Set wsChart = ThisWorkbook.Sheets("Sheet2")

    '~~> Save location
    fname = ThisWorkbook.Path & "\temp.jpg"

    '~~> Copy selection and get size
    r.CopyPicture xlScreen, xlBitmap

    '~~> Create a chart and paste the copied image to a chart
    '~~> Finally export the chart and save it as an image
    With wsChart
        Dim chtObj As ChartObject
        Set chtObj = .ChartObjects.Add(100, 30, 400, 250)

        With chtObj
            .Width = r.Width: .Height = r.Height
            .Chart.Paste
            .Chart.Export Filename:=fname, FilterName:="jpg"
            .Delete
        End With

        DoEvents
    End With

    '~~> Load the image in the image control
    Image1.Picture = LoadPicture(fname)
End Sub

In Action

Sample File

Excel Worksheet Viewer.xlsm