如何使用Excel VBA将数据从工作簿获取到用户表单组合框和文本框中?

Question

我正在尝试将数据从其他Excel工作簿获取到Userform中。因此，当从下拉列表中选择时，用户会自动填充文本框。

下面是我尝试但显示错误的代码。请帮助我解决此问题。

Private Sub cmbls_DropButtonClick()
Dim i As Long, LastRow As Long
Dim w As Workbook
Set w = Workbooks.Open("C:UsersDesktopInputs for Gate 1.xlsx")
Set ssheet = w.Worksheets("Sheet1")
'showing error in the below line LastRow'
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row  
If Me.cmbls.ListCount = 0 Then
For i = 2 To LastRow
Me.cmbls.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If
End Sub
Private Sub cmbls_Change()
Dim i As Long, LastRow As Long
Dim w As Workbook
Set w = Workbooks.Open("C:UsersInputs for Gate 1.xlsx")
Set ssheet = w.Worksheets("Sheet1")
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = (Me.cmbls) Or _
Sheets(“Sheet1”).Cells(i, “A”).Value = Val(Me.cmbls) Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “B”).Value
End If
Next
End Sub

Answer 1

该错误是由于Smart Quotes包装工作表和范围引用造成的。
使用 CTRL +F和Find and Replace All交换 ("( 和 ("(删除所有Smart Quotes，以获得正确的引号表示法 (">(。

请注意下面使用的 3 个引号之间的细微差别。VBA 需要第三个

"<>"<>">

---

以下是其他一些更新。您没有声明工作表引用，需要限定所有对象。这现在可以编译，但可能仍会产生Run Time Errors或可能存在Logic Errors。

Option Explicit
Private Sub cmbls_DropButtonClick()
Dim WB As Workbook: Set WB = Workbooks.Open("C:UsersDesktopInputs for Gate 1.xlsx")
Dim WS As Worksheet: Set WS = WB.Worksheets("Sheet1")
Dim i As Long
If Me.cmbls.ListCount = 0 Then
For i = 2 To WS.Range("A" & WS.Rows.Count).End(xlUp).Row
Me.cmbls.AddItem Sheets("Sheet1").Cells(i, "A").Value
Next i
End If
End Sub

---

Private Sub cmbls_Change()
Dim WB As Workbook: Set WB = Workbooks.Open("C:UsersInputs for Gate 1.xlsx")
Dim WS As Worksheet: Set WS = WB.Worksheets("Sheet1")
Dim i As Long
For i = 2 To WS.Range("A" & WS.Rows.Count).End(xlUp).Row
If WS.Cells(i, "A").Value = (Me.cmbls) Or WS.Cells(i, "A").Value = Val(Me.cmbls) Then
Me.TextBox1 = WS.Cells(i, "B").Value
End If
Next i
End Sub