Set Workbook Variable from Userform Combobox

Question

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

Answer 1

Your code isn't working now because CopySub doesn't know what\where ComboBox1 is. 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, CopySub will keep running.

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

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

Answer 2

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:

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