excel - 将组合框链接到 excel vba 中的文本框 - 错误 1004

Question

我在 Excel 2016 中测试了以下代码。但我遇到了

error message of 1004

并且代码不起作用。
错误行: Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False))

Private Sub UserForm_Click()
Dim xRg As Range
Private Sub UserForm_Initialize()
Set xRg = Worksheets("Sheet1").Range("A2:B8")
Me.ComboBox1.List = xRg.Columns(1).Value
End Sub

Private Sub ComboBox1_Change()
Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, 
xRg, 2, False)
End Sub

Answer 1

看来xRg声明在 ComboBox1_Change 范围之外事件。因此，Combobox1_Change()不访问它。尝试在以下范围内声明它:

Private Sub ComboBox1_Change()
    Dim xRg As Range
    Set xRg = Worksheets("Sheet1").Range("A2:B8")
    Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
                                                        xRg, 2, False)
End Sub