Show data in userform

[Diving_Dan]

Hi all,

I am having a nightmare trying to get a userform to search for data in a worksheet and display the data in the userform. I have googled and youtubed as I'm still very new to VBA and tried several different ways of trying to make it work but it won't so am having to ask.

The worksheet where I want to pull the data from is called "Hidden Stuff". It is a worksheet that holds a whole range of data for different things for the workbook.

The data I want to search for is in columns "L:Q". I have a textbox on the userform called textEmployeeID. I want to be able to add an employee ID into the textbox, click search, look to column L for that employee ID and then return the data from the row. Column M to textSurname, Column N to textFirstName, Column O to textPayNo, Column P to cboGrade and Column Q to textTelephoneNo. There are blank cells in column Q if i don't have a telephone number for the employee.

Whilst I'm asking I might as well go all out. The textboxes and combobox are locked so the data can't be edited. I have a command button that I click that unlocks them all so I can change them and then another command button to lock them again. Once i've changed any data I would like to be able to click a command button called save cmdSave to amend the data in the worksheet that I originally pulled to the userform.

I hope i'm not asking too much but i've tried so many different things and can't even get the search to work.

Thanks in advance.

 

[AlphaFrog]

This will Match the ID in column L and read the values.

    Dim rngID As Range
    
    Set rngID = Sheets("Hidden Stuff").Range("L:L").Find(textEmployeeID, , xlValues, xlWhole, 1, 1, 0)
    
    If Not rngID Is Nothing Then
        textSurname.Text = rngID.Offset(0, 1).Value
        textFirstName.Text = rngID.Offset(0, 2).Value
        textPayNo.Text = rngID.Offset(0, 3).Value
        cboGrade.Value = rngID.Offset(0, 4).Value
        textTelephoneNo.Text = rngID.Offset(0, 5).Value
        
        textEmployeeID.Tag = rngID.Address  'store the location of the matched employee ID
    Else
        MsgBox rngID.Text, vbExclamation, "No Match Found"
    End If

It stores the location of the matched ID in the .Tag property of the textEmployeeID textbox. Then use the code below to write the data to the sheet using the stored cell address in the .Tag property.

    'write to the row of the matched ID
    With Sheets("Hidden Stuff").Range(textEmployeeID.Tag)
        .Offset(0, 1).Value = textSurname.Text
        .Offset(0, 2).Value = textFirstName.Text
        .Offset(0, 3).Value = textPayNo.Text
        .Offset(0, 4).Value = boGrade.Value
        .Offset(0, 5).Value = textTelephoneNo.Text
    End With

 

[Diving_Dan]

Thanks for taking the time to write this for me, it does exactly what I want it to do.

If I try and search for an employee ID that isn't in my data I get a Run-time error '91' message. Object variable or With block variable not set.

How do I fix this error?

 

[AlphaFrog]

Show your code so i can see what changes if any you made. What line of code is highlighted when you click the Debug button on the error dialog?

 

[Diving_Dan]

I didn't change any of the code, I just pasted it straight in. The line that is being highlighted when I click debug is

MsgBox rngID.Text, vbExclamation, "No Match Found"

 

[AlphaFrog]

Try this..
MsgBox textEmployeeID.Text, vbExclamation, "No Match Found"

 

[Diving_Dan]

That now works perfectly, thank you.

 

[Diving_Dan]

Is there a way of making it that if I press the enter key whilst the cursor is in textEmployeeID that it searches as if i have clicked the cmdSearch button?

 

[AlphaFrog]

Private Sub textEmployeeID_AfterUpdate()
    Dim rngID As Range
    
    If textEmployeeID.Text = "" Then Exit Sub
    
    Set rngID = Sheets("Hidden Stuff").Range("L:L").Find(textEmployeeID, , xlValues, xlWhole, 1, 1, 0)
    
    If Not rngID Is Nothing Then
        textSurname.Text = rngID.Offset(0, 1).Value
        textFirstName.Text = rngID.Offset(0, 2).Value
        textPayNo.Text = rngID.Offset(0, 3).Value
        cboGrade.Value = rngID.Offset(0, 4).Value
        textTelephoneNo.Text = rngID.Offset(0, 5).Value
        
        textEmployeeID.Tag = rngID.Address  'store the location of the matched employee ID
    Else
        MsgBox textEmployeeID.Text, vbExclamation, "No Match Found"
    End If
End Sub