Search Userform but not on the active sheet

Wardylewis

New Member
Joined
Jun 7, 2016
Messages
37
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:

Code:
    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.
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,475
Office Version
  1. 2019
Platform
  1. Windows
Hi,
the problem you describe is with this part of your code:

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

Code:
        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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,332
Members
415,894
Latest member
silverhaze

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top