title: How to populate cell in spreadsheet from selection made in listbox in userform
body: <p>I am completely new in this and got stuck on something that sounds like a simple thing.
  I created simple user form, where assemblers will enter one of the items as a search criteria.  The listbox is then populated with all results from original spreadsheet showing the location of that part.  Assembler will then select one item that they need to pick and click the button &quot;pick&quot;.<br />
  What that will do is enter the date in &quot;PickDate&quot; in spreadsheet.  And that is where I am stuck.
  My thinking was to select the row in the spreadsheet identical to the selected row in listbox, and then create address of the cell using that row and column.  But it doesn't work.  Tried several things that I could find on internet and nothing works.  At one point I had date being entered in correct column, but not correct row.  Unfortunately, cannot remember what that code was.<br />
  Any help would be appreciated.
  Thanks a lot.  <a href="https://i.stack.imgur.com/W8x58.png" rel="nofollow noreferrer">userform</a>  <a href="https://i.stack.imgur.com/r2N7Y.png" rel="nofollow noreferrer">spreadsheet</a></p>
  <pre><code>Private Sub PickBtn_Click()
  Dim i As Integer
  
  For i = 1 To Range(&quot;A10000&quot;).End(xlUp).Row
    If Cells(i, 2) = Results.List(Results.ListIndex) Then
      Rows(i).Select
  
      .Range(Selection, 7).Value = Date
  End If
  Next i
  
  End Sub
  </code></pre>
  <p><a href="https://i.stack.imgur.com/Hidbm.png" rel="nofollow noreferrer">Entry form</a></p>
  <pre><code>Private Sub CancelJob_Click()
  'Close EntryForm form
     Unload EntryForm
  
  'Show InitialForm form
    InitialForm.Show
  End Sub
  
  Private Sub UserForm_Initialize()
  'Empty all fields
  JobBox.Value = &quot;&quot;
  Customer.Value = &quot;&quot;
  Location.Value = &quot;&quot;
  Rack.Value = &quot;&quot;
      
  'Fill combo box with product types
   With ProductCombo
      .AddItem &quot;Channel Letter Faces&quot;
      .AddItem &quot;Channel Letter Backers&quot;
      .AddItem &quot;Routed Aluminum Panels&quot;
      .AddItem &quot;Routed ACM Panels&quot;
  End With
  
  'Set focus on Work order TextBox
  JobBox.SetFocus
  End Sub
  
  
  Private Sub SubmitJob_Click()
  'Make fields mandatory
  If JobBox.Value = &quot;&quot; Or ProductCombo.Value = &quot;&quot; Or Rack.Value = &quot;&quot; Then
      If MsgBox(&quot;Cannot submit.  Please fill the mandatory fields.&quot;, 
   vbQuestion + vbOKOnly) &lt;&gt; vbOKOnly Then
      Exit Sub
      End If
  
  End If
  
  'Start transfering process
  Dim emptyRow As Long
  
  'Make Sheet1 active
  Sheet1.Activate
  
      'Determine emptyRow
      emptyRow = WorksheetFunction.CountA(Range(&quot;A:A&quot;)) + 1
      
      'Transfer information to the table
      Cells(emptyRow, 1).Value = Date 'Auto populate 1st column with submission date
      Cells(emptyRow, 2).Value = JobBox.Value
      Cells(emptyRow, 3).Value = Customer.Value
      Cells(emptyRow, 4).Value = Location.Value
      Cells(emptyRow, 5).Value = ProductCombo.Value
      Cells(emptyRow, 6).Value = Rack.Value
          
  'Save workbook after transfer of data
  ActiveWorkbook.Save
  
  'Close EntryForm
  Unload Me
  
  'Quit application so that others can use it
  'Application.Quit
  End Sub
  </code></pre>
  <p>This is complete code for this search part of the userform that I cannot
  figure out (I was playing with the code for &quot;submit&quot; button that I am stuck).  Maybe it will help for troubleshooting:</p>
  <pre><code>Private Sub ClearForm(Except As String)
  
  ' Clears the list box and text boxes EXCEPT the text box
  ' currently having data entered into it
  
  Select Case Except
  
      Case &quot;Job&quot;
          FormEvents = False
          Customer.Value = &quot;&quot;
          Location.Value = &quot;&quot;
          Results.Clear
          FormEvents = True
  
      Case &quot;Customer&quot;
          FormEvents = False
          Job.Value = &quot;&quot;
          Location.Value = &quot;&quot;
          Results.Clear
          FormEvents = True
  
      Case &quot;Location&quot;
          FormEvents = False
          Job.Value = &quot;&quot;
          Customer.Value = &quot;&quot;
          Results.Clear
          FormEvents = True
      
      Case Else
          FormEvents = False
          Job.Value = &quot;&quot;
          Customer.Value = &quot;&quot;
          Location.Value = &quot;&quot;
          Results.Clear
          FormEvents = True
          
      End Select
  
  End Sub
  
  Private Sub ClearBtn_Click()
  
  ClearForm (&quot;&quot;)
  
  End Sub
  
  Private Sub Job_Change()
  
  If FormEvents Then ClearForm (&quot;Job&quot;)
  
  End Sub
  
  Private Sub Customer_Change()
  
  If FormEvents Then ClearForm (&quot;Customer&quot;)
  
  End Sub
  
  Private Sub Location_Change()
  
  If FormEvents Then ClearForm (&quot;Location&quot;)
  
  End Sub
  
  Private Sub PickBtn_Click()
  Dim i As Integer
  
  
  Sheet1.Activate
  
  For i = 1 To Range(&quot;A10000&quot;).End(xlUp).row
  If Cells(i, 2) = Results.List(Results.ListIndex) Then
  Rows(i).Select
  
  Me.Range(&quot;Selection:G&quot;).Value = Date
      
    
  End If
  Next i
  
  
  End Sub
  
  
  
  
  
  
  
  Private Sub SearchBtn_Click()
  
  Dim SearchTerm As String
  Dim SearchColumn As String
  Dim RecordRange As Range
  Dim FirstAddress As String
  Dim FirstCell As Range
  Dim RowCount As Integer
  
  ' Display an error if no search term is entered
  If Job.Value = &quot;&quot; And Customer.Value = &quot;&quot; And Location.Value = &quot;&quot; Then
  
      MsgBox &quot;No search term specified&quot;, vbCritical + vbOKOnly
      Exit Sub
  
  End If
  
  ' Work out what is being searched for
  If Job.Value &lt;&gt; &quot;&quot; Then
  
      SearchTerm = Job.Value
      SearchColumn = &quot;Job&quot;
      
  End If
  
  If Customer.Value &lt;&gt; &quot;&quot; Then
  
      SearchTerm = Customer.Value
      SearchColumn = &quot;Customer&quot;
      
  End If
  
  If Location.Value &lt;&gt; &quot;&quot; Then
  
      SearchTerm = Location.Value
      SearchColumn = &quot;Location&quot;
      
  End If
  
  
  Results.Clear
  
      ' Only search in the relevant table column i.e. if somone is 
  searching Location
      ' only search in the Location column
      With Range(&quot;Table1[&quot; &amp; SearchColumn &amp; &quot;]&quot;)
  
          ' Find the first match
          Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
  
          ' If a match has been found
          If Not RecordRange Is Nothing Then
  
              FirstAddress = RecordRange.Address
              RowCount = 0
  
              Do
              
                  ' Set the first cell in the row of the matching value
                  Set FirstCell = Range(&quot;B&quot; &amp; RecordRange.row)
                  
                  ' Add matching record to List Box
                  Results.AddItem
                  Results.List(RowCount, 0) = FirstCell(1, 1)
                  Results.List(RowCount, 1) = FirstCell(1, 2)
                  Results.List(RowCount, 2) = FirstCell(1, 3)
                  Results.List(RowCount, 3) = FirstCell(1, 4)
                  Results.List(RowCount, 4) = FirstCell(1, 5)
                  Results.List(RowCount, 5) = FirstCell(1, 7)
                  RowCount = RowCount + 1
                  
                  ' Look for next match
                  Set RecordRange = .FindNext(RecordRange)
  
                  ' When no further matches are found, exit the sub
                  If RecordRange Is Nothing Then
  
                      Exit Sub
  
                  End If
  
              ' Keep looking while unique matches are found
              Loop While RecordRange.Address &lt;&gt; FirstAddress
  
          Else
          
              ' If you get here, no matches were found
              Results.AddItem
              Results.List(RowCount, 0) = &quot;Nothing Found&quot;
          
          End If
  
      End With
  
  End Sub
  
  
  
  Private Sub UserForm_Initialize()
  
  FormEvents = True
  
  End Sub
  </code></pre>