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 Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,492
Office Version
2013
Platform
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,089,860
Messages
5,410,836
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top