尽管运行时出现错误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