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 "pick".
What that will do is enter the date in "PickDate" 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.
Any help would be appreciated.
Thanks a lot. userform spreadsheet
Private Sub PickBtn_Click()
Dim i As Integer
For i = 1 To Range("A10000").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
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 = ""
Customer.Value = ""
Location.Value = ""
Rack.Value = ""
'Fill combo box with product types
With ProductCombo
.AddItem "Channel Letter Faces"
.AddItem "Channel Letter Backers"
.AddItem "Routed Aluminum Panels"
.AddItem "Routed ACM Panels"
End With
'Set focus on Work order TextBox
JobBox.SetFocus
End Sub
Private Sub SubmitJob_Click()
'Make fields mandatory
If JobBox.Value = "" Or ProductCombo.Value = "" Or Rack.Value = "" Then
If MsgBox("Cannot submit. Please fill the mandatory fields.",
vbQuestion + vbOKOnly) <> 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("A:A")) + 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
This is complete code for this search part of the userform that I cannot figure out (I was playing with the code for "submit" button that I am stuck). Maybe it will help for troubleshooting:
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 "Job"
FormEvents = False
Customer.Value = ""
Location.Value = ""
Results.Clear
FormEvents = True
Case "Customer"
FormEvents = False
Job.Value = ""
Location.Value = ""
Results.Clear
FormEvents = True
Case "Location"
FormEvents = False
Job.Value = ""
Customer.Value = ""
Results.Clear
FormEvents = True
Case Else
FormEvents = False
Job.Value = ""
Customer.Value = ""
Location.Value = ""
Results.Clear
FormEvents = True
End Select
End Sub
Private Sub ClearBtn_Click()
ClearForm ("")
End Sub
Private Sub Job_Change()
If FormEvents Then ClearForm ("Job")
End Sub
Private Sub Customer_Change()
If FormEvents Then ClearForm ("Customer")
End Sub
Private Sub Location_Change()
If FormEvents Then ClearForm ("Location")
End Sub
Private Sub PickBtn_Click()
Dim i As Integer
Sheet1.Activate
For i = 1 To Range("A10000").End(xlUp).row
If Cells(i, 2) = Results.List(Results.ListIndex) Then
Rows(i).Select
Me.Range("Selection:G").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 = "" And Customer.Value = "" And Location.Value = "" Then
MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub
End If
' Work out what is being searched for
If Job.Value <> "" Then
SearchTerm = Job.Value
SearchColumn = "Job"
End If
If Customer.Value <> "" Then
SearchTerm = Customer.Value
SearchColumn = "Customer"
End If
If Location.Value <> "" Then
SearchTerm = Location.Value
SearchColumn = "Location"
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("Table1[" & SearchColumn & "]")
' 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("B" & 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 <> FirstAddress
Else
' If you get here, no matches were found
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub
Private Sub UserForm_Initialize()
FormEvents = True
End Sub
Add another column in the list box to hold the row number.
Results.List(RowCount, 6) = FirstCell.Row
And then code becomes
Private Sub PickBtn_Click()
Dim r as long
r = Results.List(Results.ListIndex,6)
Range(r, 7).Value = Date
End Sub