vba - 使用用户窗体中的文本框来定义变量？

Question

我当前运行一个宏，将最新的数据表与之前的报告进行比较，并突出显示更改。它本身工作得很好。然而现在，我们希望能够比较任何时间段的选定工作表。我的想法是弹出一个简单的用户表单，其中包含两个文本框，用户可以使用它们来指定他想要比较哪两个报告。尽管我对尝试声明公共(public)变量的想法感到非常迷失；我有 atm 是:

Option Explicit
Public shtNew As String, shtOld As String, _
TextBox1 As TextBox, TextBox2 As TextBox

Sub SComparison()

    Const ID_COL As Integer = 31 'ID is in this column
    Const NUM_COLS As Integer = 31 'how many columns are being compared?

    Dim rwNew As Range, rwOld As Range, f As Range
    Dim X As Integer, Id

    shtNew = CSManager.TextBox1
    shtOld = CSManager.TextBox2

    'Row location of the first employee on "CurrentMaster" sheet
    Set rwNew = shtNew.Rows(5)

    Do While rwNew.Cells(ID_COL).Value <> ""

        Id = rwNew.Cells(ID_COL).Value
        Set f = shtOld.UsedRange.Columns(ID_COL).Find(Id, , xlValues, xlWhole)

        If Not f Is Nothing Then

            Set rwOld = f.EntireRow

            For X = 1 To NUM_COLS
                If rwNew.Cells(X).Value <> rwOld.Cells(X).Value Then
                    rwNew.Cells(X).Interior.Color = vbYellow
                    rwNew.Cells(33) = "UPDATE"
                Else
                    rwNew.Cells(X).Interior.ColorIndex = xlNone
                End If
            Next X

        End If

        Set rwNew = rwNew.Offset(1, 0) 'next row to compare

        Loop

        Call SUpdates
End Sub

Answer 1

我的建议是使用组合框而不是文本框。创建一个包含两个命令按钮和两个组合框的用户窗体，并使用此代码填充 UserForm_Initialize() 事件中的组合框。

Private Sub UserForm_Initialize()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Sheets
        ComboBox1.AddItem ws.Name: ComboBox2.AddItem ws.Name
    Next
End Sub

然后在确定按钮中使用此代码进行比较。

Private Sub CommandButton1_Click()
    Dim shtNew As Worksheet, shtOld As Worksheet

    If ComboBox1.ListIndex = -1 Then
        MsgBox "Please select the first sheet"
        Exit Sub
    End If

    If ComboBox2.ListIndex = -1 Then
        MsgBox "Please select the Second sheet"
        Exit Sub
    End If

    Set shtNew = Sheets(ComboBox1.Value)
    Set shtOld = Sheets(ComboBox2.Value)

    '~~> REST OF THE CODE HERE NOW TO WORK WITH THE ABOVE SHEETS
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

HTH

席德