title: How to use a UserForm to select a worksheet from selected workbook
body: <p>I am trying to create a macro which allows a user to update a master workbook (where the macro is located) from a specific workbook and worksheet. The workbook and worksheet names can vary, so I call this UserForm from code adapted from <a href="http://www.ozgrid.com/forum/showthread.php?t=192585" rel="nofollow noreferrer">the ozgrid forum</a>:</p>
  
  <pre><code>Option Explicit
  
  Private Sub CommandButton1_Click()
      MyFile = Me.ComboBox1.Value
      MySheet = Me.ComboBox2.Value
      Unload Me
  End Sub
  
  Private Sub CommandButton2_Click()
      Stopped = True
      Unload Me
  End Sub
  
  Private Sub UserForm_Initialize()
      Dim wkb As Workbook
      Dim sht As Worksheet
      Me.Label1.Caption = "Please select one of the following files and Worksheets..."
      With Me.ComboBox1
          For Each wkb In Application.Workbooks
              .AddItem wkb.Name
          Next wkb
      End With
      MyFile = Me.ComboBox1.Value
      With Me.ComboBox2
          For Each sht In Application.Worksheets
              .AddItem sht.Name
          Next sht
      End With
      MySheet = Me.ComboBox2.Value
  End Sub
  </code></pre>
  
  <p>Now the problem is with the module in the master workbook that calls the user form. I get a type mismatch error with this bit: Set wb1 = MyFile</p>
  
  <p>Here is the rest of the module:</p>
  
  <pre><code>Option Explicit
  
  Public MyFile As String
  Public Stopped As Boolean
  
  Sub Update_Master()
      Stopped = False
      UserForm1.Show
      If Stopped Then Exit Sub
      MsgBox MyFile
  
  ' Update_Master Macro
  '
  ' Keyboard Shortcut: Ctrl+m
  '
      Dim wb1 As Workbook, wb2 As Workbook
      Dim TargetFile As Variant
      Dim ws1 As Worksheet, ws2 As Worksheet
      Dim ws1LRow As Long, ws2LRow As Long
      Dim i As Long, j As Long
      Dim ws1LCol As Long, ws2LCol As Long
      Dim aCell As Range, bCell As Range
      Dim SearchString As String
      Dim ExitLoop As Boolean, matchFound As Boolean
  
      '~~&gt; Set EOD Workbook
      Set wb1 = MyFile
      Set ws1 = MySheet
      '~~&gt; Get the last Row and Last Column
      With wb1
          ws1LRow = .Range("E" &amp; .Rows.Count).End(xlUp).Row
          ws1LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      End With
  
      '~~&gt; Set Master Workbook
      Set wb2 = Workbooks("MoO - Master List - TEST.xlsm")
      Set ws2 = wb2.Sheets("CM List")
      '~~&gt; Get the last Row and Last Column
      With ws2
          ws2LRow = .Range("E" &amp; .Rows.Count).End(xlUp).Row
          ws2LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      End With
  
      '~~&gt; Loop Through Cells of Col E in workbook A and try and find it
      '~~&gt; in Col E of workbook B
      For i = 2 To ws1LRow
          SearchString = ws1.Range("E" &amp; i).Value
  
          Set aCell = ws2.Columns(5).Find(What:=SearchString, LookIn:=xlValues, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
  
          ExitLoop = False
  
          '~~&gt; If match found
          If Not aCell Is Nothing Then
              Set bCell = aCell
  
              matchFound = True
  
              '~~&gt; Then compare all columns
              For j = 6 To ws1LCol
                  If ws1.Cells(i, j).Value &lt;&gt; ws2.Cells(aCell.Row, j).Value Then
                      matchFound = False
                      Exit For
                  End If
              Next
  
              '~~&gt; If all columns matched then write to Col A/B
              If matchFound = False Then
                  ws2.Cells(aCell.Row, 12).Value = ws1.Cells(i, 12).Value
                  ws2.Cells(aCell.Row, 13).Value = ws1.Cells(i, 13).Value
              End If
  
              '~~&gt; Find Next Match
              Do While ExitLoop = False
                  Set aCell = ws2.Columns(5).FindNext(After:=aCell)
  
                  '~~&gt; If match found
                  If Not aCell Is Nothing Then
                      If aCell.Address = bCell.Address Then Exit Do
  
                      matchFound = True
  
                      '~~&gt; Then compare all columns
                      For j = 6 To ws1LCol
                          If ws1.Cells(i, j).Value &lt;&gt; ws2.Cells(aCell.Row, j).Value Then
                              matchFound = False
                              Exit For
                          End If
                      Next
  
                      '~~&gt; If all columns matched then write to Col A/B
                      If matchFound = False Then
                          ws2.Cells(aCell.Row, 12).Value = ws1.Cells(i, 12).Value
                          ws2.Cells(aCell.Row, 13).Value = ws1.Cells(i, 13).Value
                      End If
                  Else
                      ExitLoop = True
                  End If
              Loop
          End If
      Next
  End Sub
  </code></pre>
  
  <p>Any help would be greatly appreciated. I am an amateur and have been trying to piece this together from snippets of code I found here and elsewhere. </p>
  
  <p>Thanks in advance!</p>