vba 从用户窗体组合框设置工作簿变量

Set Workbook Variable from Userform Combobox

提问by postelrich

I am creating a macro for my co-workers. They get a file daily and at the end of the day have to copy certain information to another workbook. The macro is to take care of the copying. I want to have a userform with a combobox popup that contains a list of current open workbooks so it knows which file to copy from. How do I set it up so that the selection made there sets a workbook variable with that selection?

我正在为我的同事创建一个宏。他们每天都会收到一个文件，并且在一天结束时必须将某些信息复制到另一个工作簿中。宏是负责复制的。我想要一个带有组合框弹出窗口的用户表单，其中包含当前打开的工作簿列表，以便它知道要从哪个文件复制。我如何设置它以便在那里进行的选择设置一个带有该选择的工作簿变量？

What I'm trying to do is:

我想要做的是：

Sub CopySub()

Dim wb As Workbook

UserForm1.Show

Set wb = Workbooks(ComboBox1.Value)
....Rest of Copy and Paste Code

Below is the code for the userform:

下面是用户表单的代码：

Private Sub OK_Click()
'Take user selection and continue copy and paste code
UserForm1.Hide
End Sub

Private Sub Cancel_Click()
'Cancel everything, end all code
End
End Sub


Private Sub UserForm_Activate()

  'Populate list box with names of open workbooks.

  Dim wb As Workbook

  For Each wb In Workbooks

    ComboBox1.AddItem wb.Name

  Next wb

End Sub

回答by mischab1

Your code isn't working now because CopySubdoesn't know what\where ComboBox1is. Also, if the user clicks the form's X to close it instead of pressing the cancel button or clicks the OK button without selecting a workbook, CopySubwill keep running.

您的代码现在无法运行，因为CopySub不知道是什么\在哪里ComboBox1。此外，如果用户单击窗体的 X 关闭它而不是按取消按钮或单击确定按钮而不选择工作簿，CopySub将继续运行。

There are a couple different ways to get the form information. The simplest with your current code is to properly reference ComboBox1and add a simple test.

有几种不同的方法可以获取表单信息。您当前的代码最简单的是正确引用ComboBox1并添加一个简单的测试。

Sub CopySub()
   Dim wb As Workbook

   UserForm1.Show
   If UserForm1.ComboBox1.Value = "" Then
      Exit Sub
   End If

   Set wb = Workbooks(UserForm1.ComboBox1.Value)
   ' rest of code goes here

End Sub

Something else to think about though is ways to make your macro quicker and easier to run. If the only thing on your form is a Combobox for selecting the workbook and users will be starting the macro from a keyboard-shortcut or from the menu, consider having the macro ask if they want to run the macro on the active workbook. Clicking Yes to a question is a lot faster than having to click a dropdown box, select the workbook, and then click OK.

其他需要考虑的方法是让您的宏更快更容易运行。如果表单上只有一个用于选择工作簿的组合框，并且用户将从键盘快捷键或菜单启动宏，请考虑让宏询问他们是否要在活动工作簿上运行宏。对问题单击“是”比单击下拉框、选择工作簿，然后单击“确定”要快得多。

Sub CopySub()
   Dim wb As Workbook

   If MsgBox("Do you want to run the macro on '" & ActiveWorkbook.Name & "'?", vbQuestion + vbYesNo) = vbYes Then
      Set wb = ActiveWorkbook
   Else
      UserForm1.Show
      If UserForm1.ComboBox1.Value = "" Then
         Exit Sub
      End If
      Set wb = Workbooks(UserForm1.ComboBox1.Value)
   End If

   ' rest of code goes here

End Sub

回答by postelrich

After further searching I found the answer, and its the same as what mischab points out, I didn't create a global variable so there was no way for my userform to communicate with the subroutine. I solved this by declaring a variable with scope for the whole workbook as such:

进一步搜索后，我找到了答案，这与 mischab 指出的相同，我没有创建全局变量，因此我的用户表单无法与子例程进行通信。我通过声明一个具有整个工作簿范围的变量来解决这个问题：

Public wb1 As String

Sub CopySub()

Dim wbCAR As Workbook

UserForm1.Show

Set wbCAR = Workbooks(wb1)
....Rest of code

and by setting the userform code to such:

并通过将用户表单代码设置为：

Private Sub OK_Click()
wb1 = ComboBox1.Value
UserForm1.Hide
End Sub

Private Sub Cancel_Click()
Unload Me
End
End Sub


Private Sub UserForm_Activate()

  'Populate list box with names of open workbooks.
  Dim wb As Workbook
  For Each wb In Workbooks
    ComboBox1.AddItem wb.Name
  Next wb

End Sub