vba - 使用来自另一个工作表的动态值填充 ComboBox

Question

- -更新 - -

感谢您的回复，我发现 DragonSamu 的更新答案非常有效。

---原帖---

在过去的几个小时里，我一直试图找出我哪里出错了，但我无法发现它。我认为这是因为脚本试图从事件工作表中提取值，这不是我想要的。希望有人能把我放在仪式轨道上——我认为答案应该是相对明显的，但我就是看不到!

基本上，我试图用另一个工作表(但在同一个工作簿中)中存在的动态值范围填充组合框。当我在工作表“ Material ”(这是动态列表的来源)中运行脚本时，我可以填充组合框，但在工作表“产品”中运行它时却不能。

不幸的是，该脚本旨在用 Material 填充产品，因此当“产品”工作表打开并且“ Material ”工作表将处于非事件状态时，它会在用户窗体中运行。

我还应该注意，这个脚本是根据我在这个论坛其他地方找到的代码改编的，所以如果它看起来很熟悉，我提前感谢你 :)

Private Sub UserForm_Initialize()

Dim rRange As Range

On Error GoTo ErrorHandle

'We set our range = the cell B7 in Materials
Set rRange = Worksheets("Materials").Range("B7")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'The range's address is our rowsource
Mat1_Name_ComBox.RowSource = rRange.Address
Mat2_Name_ComBox.RowSource = rRange.Address
Mat3_Name_ComBox.RowSource = rRange.Address
Mat4_Name_ComBox.RowSource = rRange.Address
Mat5_Name_ComBox.RowSource = rRange.Address

BeforeExit:
Set rRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit

End Sub

任何帮助深表感谢。

干杯，

西蒙

Answer 1

从我可以看到你的代码会在这里给出一个错误:

If Len(rRange.Offset(1, 0).Formula) > 0 Then
    Set rRange = Range(rRange, rRange.End(xlDown))
End If

因为你试图设置 rRange通过使用 Range()没有定义 Worksheet第一的。这将得到 Range来自 ActiveWorksheet .

将其更改为以下内容:

If Len(rRange.Offset(1, 0).Formula) > 0 Then
    Set rRange = Worksheets("Materials").Range(rRange, rRange.End(xlDown))
End If

最佳做法如下:

Private Sub UserForm_Initialize()

Dim wb as Workbook
Dim sh as Worksheet
Dim rRange As Range

On Error GoTo ErrorHandle

'Set the Workbook and Worksheet
set wb = Workbooks("products.xlsx")
set sh = wb.Worksheets("Materials")

'We set our range = the cell B7 in Materials
Set rRange = sh.Range("B7")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = sh.Range(rRange, rRange.End(xlDown))
End If

通过正确定义和设置您的 Workbook和 Worksheet您正确引用它们并且不会出错。

更新:

第二个问题是 rRange.Address只放置 Range您的 .RowSource 内的位置不是 Sheet它需要看看。

改变:

Mat1_Name_ComBox.RowSource = rRange.Address

至:

dim strSheet as String
strSheet = "Materials"
Mat1_Name_ComBox.RowSource = strSheet + "!" + rRange.Address

这样它将包含 Sheet名称进入 .RowSource