在用户窗体上显示Excel工作簿 [英] Display Excel Workbook on a Userform

问题描述

目标:如标题所示，我们如何在不使用任何第三方控件的情况下在用户窗体上显示工作簿.

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

通过 Display ，我的意思是在用户窗体上显示它，用户可以在其中选择工作表并查看该工作表的内容.

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.

推荐答案

我将在下面演示的方法将不使用任何第三方控件.实际上，它将在图像控件中显示工作表.显然，这意味着您无法与工作表进行交互.它仅用于显示工作表中的数据.

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.

基本设置

创建一个用户窗体并放置控件，如下所示.我在帖子末尾提供了一个示例文件.随意修改并使其变得更好.也可以随意调整用户表单的大小以适合您的需求.

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.

代码

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

实际操作

示例文件

Excel Worksheet Viewer.xlsm

这篇关于在用户窗体上显示Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！