尽管运行时出现错误91,但Userform仍可正常工作。到底怎么回事?

我刚接触Userforms。我已经创建了下面的用户表单,它从子例程中调用。用户表单从工作表中选取一个范围,并创建相应数量的文本框,然后创建复选框,以便为原始名称分配新名称。

?

?

用户表单是使用以下内容创建的:

Public Sub UserForm_Initialize()

    'Declare variables
    Dim txtBox As MSForms.TextBox
    Dim comBox As MSForms.ComboBox
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    Dim dist As Integer
    Dim dstArr As Variant
    Dim rng As Range
    
    'Assign variables
    Set rng = Range("Missing_MAERSK")
    n = rng.Rows.Count
    dist = 5
    dstArr = Range("LU_Destination_Ports").Value
    
    'Loop to add textboxes
    For i = 1 To n
        Set txtBox = UserForm1.Controls.Add("Forms.TextBox.1", Visible:=True)
        With txtBox
            .name = "txtBox" & i
            .Value = rng(i)
            .Height = 20
            .Width = 150
            .Left = 81
            .Top = 30 + dist
            .Font.Size = 10
        End With
        dist = dist + 20
    Next i
    
    'Loop to add list boxes
    dist = 5
    For j = 1 To n
        Set comBox = UserForm1.Controls.Add("Forms.ComboBox.1", Visible:=True)
        With comBox
            .name = "comBox" & j
            .List = dstArr
            .Height = 20
            .Width = 150
            .Left = 315
            .Top = 30 + dist
            .Font.Size = 10
        End With
        dist = dist + 20
    Next j
    
    'Show userform
    UserForm1.Show
    
End Sub

然后,当单击Replace Names按钮时,将运行以下命令:

Public Sub CommandButton1_Click()

    'Close userform
    Unload UserForm1
    
    'This is the one
    Dim cmb As MSForms.ComboBox
'   Dim txt As MSForms.TextBox
    Dim oldVal As String
    Dim newVal As String
    Dim rng As Range
    Dim rng2 As Range
    Dim n As Integer
    Set rng = Range("MAERSK_Destin")
    Set rng2 = Range("Missing_MAERSK")
    n = rng2.Rows.Count
    
    'Loop
    For i = 1 To n
        Set txt = Me.Controls("txtBox" & i)
        Set cmb = Me.Controls("comBox" & i)
            If cmb.Value <> "" Then
                oldVal = txt.Value
                newVal = cmb.Value
                rng.Replace what:=oldVal, Replacement:=newVal
            End If
    Next i
    
End Sub

假设我从曼谷到曼谷BMT,我得到了以下结果:

?

?

我认为问题可能出在我调用Command_Button1_Click sub中的值的方式上。

任何建议都将不胜感激。

干杯

转载请注明出处:http://www.xinxianzhihui.com/article/20230526/1730651.html