确保分配RowSource不会失败 [英] Ensure that assigning RowSource will not fail

问题描述

我有2个ComboBoxes，我使用 RowSource 填充。

Combobox2 RowSource 关于Combobox1的价值。

I have 2 ComboBoxes which I populate using RowSource.
Combobox2 RowSource change based on the value of Combobox1.

我有这个代码：

If Combobox1.Value = "Item1" Then Combobox2.RowSource = "Sheet1!Item1"

Sheet1 是工作表的名称， Item1 是一个命名范围。

Sheet1 is the name of the sheet, Item1 is a named range.

问题：

如果打开的唯一工作簿是包含此代码的工作簿，则此工作正常。

但是，如果另一个工作簿打开，它没有正确地引用 RowSource 。

我该如何改进？

Problem:
This works fine if the only workbook open is the one containing this code.
However, if another workbook is open, it fails to reference the RowSource correctly.
How can I improve this?

推荐答案

为了改善这一点，请包含包含 RowSource 的工作簿的文件名，如下所示：

To improve this, include the Filename of the workbook that contains the RowSource like this:

If ComboBox1.Value = "Item1" Then ComboBox2.RowSource = "'[Test.xlsm]Sheet1'!Item1"

或者通过声明变量来改进它：

Or better yet improve it by declaring variables:

Dim wb As Workbook, ws As Worksheet

Set wb = Thisworkbook
Set ws = wb.Sheets("Sheet1")

If ComboBox1.Value = "Item1" Then _
   ComboBox2.RowSource = "'[" & wb.Name & "]" & ws.name & "'!" & "Item1"

这篇关于确保分配RowSource不会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！