Search Userform but not on the active sheet

[Wardylewis]

Good Morning All,

I am new to the world of userform's and think they are pretty dam amazing. I am currently struggling with a small piece of code and was wondering if somebody could guide me.

I have a userform where users need to input results for an employee. The user inputs the employee ID and then it returns their first name and surname from the "Employeelist" sheet.

The user will then insert the test results and it will add the information to a new row in the "results" sheet when the command button is pressed.

Currently, the search function is working but only if I'm on the "Employeelist" sheet once I come off it, it returns blank boxes and no error messages.

The two options I have thought about but need help for both are:

1. Have the VBA code to open the sheet the data is on then do the search. Then change back when the user hits the add button.
2. Change the code to search the specific sheet and return the results.

What do you believe is the best way?

My code so far is:

    Dim ID As Range    Dim IDRow As Long
    Dim BV As String, EID As String


    EID = Me.TextBox1.Value
    Set ID = Worksheets("Employeelist").Range("A:A").Find(What:=EID, LookAt:=xlWhole)
    If ID Is Nothing Then
        MsgBox "Error, Value Not Found!", vbCritical + vbOKOnly, "ERROR"
        TextBox1.Value = ""
       Exit Sub
      End If
    IDRow = ID.Row


    TextBox2.Text = Cells(IDRow, 2).Text
    TextBox3.Text = Cells(IDRow, 3).Text

Thank you for any help in advance.

 

[dmt32]

Hi,
the problem you describe is with this part of your code:

Cells(IDRow, 2).Text
Cells(IDRow, 3).Text

the ranges are not qualified to a specific worksheet.

You can resolve by using the Range object variable you have defined as follows

        TextBox2.Text = ID.Offset(, 1).Text
        TextBox3.Text = ID.Offset(, 2).Text

which should hopefully return the correct values even if the sheet is not the active sheet.

Dave

 

[Wardylewis]

Hi @dmt32 thank you for that it has worked perfectly.