vba Excel 宏 - Windows().Activate 不取值

Excel Macro - Windows().Activate not taking value

提问by Gek

The code that works is the following:

有效的代码如下：

Windows("Contract Drilldown (3).xls").Activate

When I use :

当我使用：

 Windows(Chr(34) & ddlOpenWorkbooks.Value & Chr(34)).Activate

I get:

我得到：

Runtime Error '424': Object Required

运行时错误“424”：需要对象

---

---

If I use a String Variable to pass in the values i.e.:

如果我使用字符串变量传递值，即：

Dim wbn As String
wbn = "Contract Drilldown (3).xls"
Windows(Chr(34) & wbn & Chr(34)).Activate

I get:

我得到：

Run-time error '9': Subscript out of range

运行时错误“9”：下标超出范围

---

---

And if I use

如果我使用

wbn = ddlOpenWorkbooks.Value
Windows(Chr(34) & wbn & Chr(34)).Activate

I also get

我也得到

Runtime Error '424': Object Required

运行时错误“424”：需要对象

---

---

Anyone have any idea how I can pass in the ddlOpenWorkbooks.Valuein without getting an error?

任何人都知道如何在不出错的情况下传入ddlOpenWorkbooks.Value？

Edit - More Info

编辑 - 更多信息

Ok so the application looks like this:

好的，应用程序看起来像这样：

the full code block for the Import Data Button is:

导入数据按钮的完整代码块是：

Public Sub Data_Import()

    Windows(ddlOpenWorkBooks.Value).Activate
    Columns("A:V").Select
    Selection.Copy
    Omni_Data.Activate
    Range("A1").Select
    ActiveSheet.Paste

    Omni_Data.Range("A:Z").Interior.ColorIndex = 0
    Omni_Data.Range("A:Z").Font.Name = "Segoe UI"
    Omni_Data.Range("A:Z").Font.Name = "Segoe UI"
    'Setting Background Colour to white and changing font

End Sub

The above Sub is called on Click Event for the button.

上面的 Sub 是在按钮的 Click 事件上调用的。

As a test the close button has the following code:

作为测试，关闭按钮具有以下代码：

Private Sub cmdCancel_Click()
    MsgBox (ddlOpenWorkbooks.Value)
    End

End Sub

Which works fine:

哪个工作正常：

Update

更新

So we have found the problem.

所以我们找到了问题所在。

As this is being called from a module it didn't know where ddlOpenWorkbooks was and where to pull that data from.

由于这是从模块调用的，它不知道 ddlOpenWorkbooks 在哪里以及从哪里提取数据。

The corrected code in the Sub is:

Sub中更正后的代码是：

Public Sub Data_Import()

    Windows(frmOmniDataManipulation.ddlOpenWorkbooks.Value).Activate
    Columns("A:V").Select
    Selection.Copy
    Omni_Data.Activate
    Range("A1").Select
    ActiveSheet.Paste

    Omni_Data.Range("A:Z").Interior.ColorIndex = 0
    Omni_Data.Range("A:Z").Font.Name = "Segoe UI"
    Omni_Data.Range("A:Z").Font.Name = "Segoe UI"
    'Setting Background Colour to white and changing font

End Sub

This will allow me to call the sub.

这将允许我调用 sub。

Thanks All!

谢谢大家！

采纳答案by guitarthrower

have you tried:

你有没有尝试过：

Windows(ddlOpenWorkbooks.Value).Activate

The quotes are only needed if you are typing in the value. If you are passing the value in some other way the quotes are unnecessary.

仅当您输入值时才需要引号。如果您以其他方式传递值，则不需要引号。

UPDATE

更新

Here are the steps I have taken, and they allow me to activate the selected window with no errors.

以下是我采取的步骤，它们使我可以毫无错误地激活所选窗口。

I created a Userform with a ComboBox named ddlOpenWorkbooksand 2 buttons.

我创建了一个带有 ComboBox 命名ddlOpenWorkbooks和 2 个按钮的用户窗体。

I hardcoded the RowSource of my ComboBox for illustration purposes, but that shouldn't be the issue here.

出于说明目的，我对 ComboBox 的 RowSource 进行了硬编码，但这不应该是这里的问题。

My 1st button displays a message with the selected workbooks name from ddlOpenWorkbooks.

我的第一个按钮显示一条消息，其中包含来自ddlOpenWorkbooks.

Here is the code for the button

这是按钮的代码

Private Sub btnMessage_Click()
    MsgBox Me.ddlOpenWorkbooks.Value
End Sub

Here is the result

这是结果

My second button uses the selection in ddlOpenWorkbooksand activates that window. Here is the code.

我的第二个按钮使用其中的选择ddlOpenWorkbooks并激活该窗口。这是代码。

Private Sub btnActivate_Click()
    Windows(Me.ddlOpenWorkbooks.Value).Activate
End Sub

And here is the result

这是结果

FINAL UPDATE:

最终更新：

When passing the ddlOpenWorkbooks.Value to another procedure that is not a UserForm event, you'll need to include the name of your UserForm.

将 ddlOpenWorkbooks.Value 传递给不是用户窗体事件的另一个过程时，您需要包含用户窗体的名称。

This is the line you'll need in your procedure to correctly get the value from the ComboBox:

这是您在程序中正确获取 ComboBox 值所需的行：

Windows(frmOmniDataManipulation.ddlOpenWorkbooks.Value).Activate

回答by cronos2546

If this is combobox, then I believe that

如果这是组合框，那么我相信

ddlOpenWorkbooks.List(ddlOpenWorkbooks.ListIndex) 

will return the selected value. Alternatively, you can pass in the index value with

将返回选定的值。或者，您可以传入索引值

ddlOpenWorkbooks.List(#)