Search code examples
excelvbauserform

Send Textbox Data to a specific row/cell in a Worksheet based on the Value of a ComboBox

I built a userform that captures data on how long it takes a worker to do a job.

I created a second form to lookup the reference and enter the time he/she finished the job.

I can send all data from the first form to the worksheet 'Tracker' and also lookup the 'Job Reference' to populate the data on the second userform. I cannot however send the 'End Time' to the row that relates to the 'Job Reference' identifier.

I tried using the same code I used to find an empty row and insert data in the first form, but it enters the end time in the next empty row.

enter image description here

Private Sub CommandButton1_Click()

endTimeTxt.Value = Time

End Sub

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

    'Defines worksheet and Database Var
Dim findRng As Range
Dim lookup As String
lookup = Trim(Application.InputBox("What ID do you want to find?"))
Set findRng = Range("G5:G1000").Find(what:=lookup)

If Not findRng Is Nothing Then
    Debug.Print "The row to use is: " & findRng.Row
Else
    MsgBox (lookup & "was not found in column A!")
End If

Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim xRg As Range
    Set xRg = Worksheets("Lists ").Range("I2:P21")
    Me.jobRefCbo.List = xRg.Columns(1).Value
End Sub

Private Sub jobRefCbo_Change()

    'Formatting Issue
    jobCloseFrm.date2Txt.Value = Format(Range("W1").Value, "dd/mm/yyyy")
        Me.nameTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 2, False)
        Me.jobDesc2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 3, False)
        'Me.date2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 4, False)
        Me.month2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 5, False)
        Me.timeOnJobTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 6, False)
        Me.StatusTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 7, False)
        Me.startTime2Txt.Value = Format(CDate(Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I2:P21"), 8, False)), "hh:mm:ss AM/PM")

End Sub

I was at the point of trying code I googled which is why the code to send the Time End doesn't make much sense.

Solution

  • Broadly speaking, something like this

    'in click event

Dim findRng As Range

Set findRng = Range("A5:A1000").Find(what:=Me.combobox1.Value) 'insert name of combobox here

If Not findRng Is Nothing Then
    Cells(findRng.Row, 9).Value = Me.textbox1.Value            'insert name of textbox here
End If