我已经在VBA中创建了一个宏，该宏找到单词“ Viewpoint”，并生成一个用户窗体，该窗体在周围的单元格中查找（向“北”，“东”，“南”或“西”）并进行转换只要该单元格是“可转换的”，就可以将4个字母的代码转换成4位数字。

它可以在周围单元格中使用数组来工作。例如，以下内容将允许用户转换“北部”和“东部”单元格，但不会转换“西部”（即“不可转换”），并且不会为“南部”产生任何结果：

在此示例中，加载宏后，向用户显示以下用户表单。单击“向北转换”或“向东转换”按钮会产生一个带有转换代码的消息框，而单击“向西转换”会产生一个带有“不可转换”字样的消息框。

但是，我不想生成一个消息框，而是要生成第二个用户表单，基于用户从第一个用户表单中的选择。例如如果用户选择“向北转换”，他们将收到带有转换后的代码（作为标签）和两个其他选项（作为按钮，理想情况下仅基于数组中的其他索引）的其他用户窗体。当然，这意味着将需要某种方式在第二用户表单中引用第一用户表单。我在这里查看了其他答案，但似乎找不到在这里可用的答案。这样做的一个好方法是什么？

这是到目前为止的模块代码。请注意，“ optNorth”，“ optsouth”等是指用户窗体按钮，而“ lblNorth”等是指用户窗体标签。最后，请随时告诉我是否可以通过某种方式使此代码更有效：

Private Sub Surroundings(OFFSET_CELL As Range,LBL As Object,OPT As Object,DIRECTION As String)

    Dim VIEWPOINT As Range
    Dim LAST_ROW As Integer
    Dim ARR() As String

    ' Set ARR to use the "Split" function in the destination cell,making the "\" a delimiter
    ARR = Split(OFFSET_CELL,"\")

    ' Set the range in which to search for "Viewpoint"
    Set VIEWPOINT = Sheet1.Range("A1:Z26").Find(What:="Viewpoint",LookAt:=xlWhole,MatchCase:=True)

    ' If there is a "\" in the adjacent cell...
    If InStr(1,OFFSET_CELL,"\") > 0 Then
        ' show a label with the DIRECTION and array index 0 (the 4-letter code)
        LBL.Caption = DIRECTION & " is " & (ARR(0))
        ' show a button label with the word "Convert" and the DIRECTION:
        OPT.Caption = "Convert " & DIRECTION

    ' In any other situation...
    Else
        LBL.Caption = DIRECTION & " is nothing."
        OPT.Caption = "Nothing"
        OPT.Enabled = False
    End If

End Sub

Public Sub Surroundings_Userform()

    Dim VIEWPOINT As Range

    ' Set the range in which to search for "Viewpoint"
    Set VIEWPOINT = Sheet1.Range("A1:Z26").Find(What:="Viewpoint",MatchCase:=True)

    ' Load up the userform
    Load SurroundingsUserForm

    ' Call the variable code in the Surroundings sub
    Call Surroundings(VIEWPOINT.Offset(-1,0),SurroundingsUserForm.lblNorth,SurroundingsUserForm.optNorth,"North")
    Call Surroundings(VIEWPOINT.Offset(1,SurroundingsUserForm.lblSouth,SurroundingsUserForm.optsouth,"South")
    Call Surroundings(VIEWPOINT.Offset(0,1),SurroundingsUserForm.lblEast,SurroundingsUserForm.optEast,"East")
    Call Surroundings(VIEWPOINT.Offset(0,-1),SurroundingsUserForm.lblWest,SurroundingsUserForm.optWest,"West")

    SurroundingsUserForm.Show

    Set VIEWPOINT = Nothing

End Sub

这是表单代码：

Private Sub OptionButton(OPT_BUTTON As String,OFFSET_ROW As Integer,OFFSET_COLUMN As Integer)

    Dim VIEWPOINT As Range
    Dim ARR() As String
    Dim OFFSET_CELL As String

    ' Set the range in which to search for "Viewpoint")
    Set VIEWPOINT = Sheet1.Range("A1:Z26").Find(What:="Viewpoint",MatchCase:=True)

    Select Case True
        Case OPT_BUTTON
            ' Define OFFSET_CELL as per the option button
            OFFSET_CELL = VIEWPOINT.Offset(OFFSET_ROW,OFFSET_COLUMN)
            ' The array uses "\" as a delimiter
            ARR = Split(OFFSET_CELL,"\")
                ' If array index 1 is "Convertible",show a message box with array index 2
                If ARR(1) = "Convertible" Then
                    MsgBox (ARR(2))
                ' If array index 1 is "Nonconvertible",show a message box with "Nonconvertible"
                ElseIf ARR(1) = "Nonconvertible" Then
                    MsgBox ("Nonconvertible")
                ' In any other situation,unload the userform
                Else
                    Unload Me
                End If
    End Select

    Unload Me

End Sub

Private Sub optNorth_Click()
    Call OptionButton(optNorth,-1,0)
End Sub

Private Sub optsouth_Click()
    Call OptionButton(optsouth,1,0)
End Sub

Private Sub optEast_Click()
    Call OptionButton(optEast,1)
End Sub

Private Sub optWest_Click()
    Call OptionButton(optWest,-1)
End Sub

Private Sub optClose_Click()
    Unload Me
End Sub