We help IT Professionals succeed at work.

Searching for names in Excel workbook

252 Views
Last Modified: 2012-05-11
I have an excel workbook that contains an organizational chart on each tab.  The organizational charts are made up of text boxes that contain the person's name and their position.  is there a way that i can create a find function on the first page of the workbook and then enter the person's name that i am looking for and the find function find the name, going through all worksheets until it finds it?
Comment
Watch Question

Author

Commented:
The names that i am searching for are in a text box.  Is there a way to perform a search on a workbook, on all text boxes in that workbook?
Gk

Commented:
Search function helps you.
If you want by VB, refer this page:
http://msdn.microsoft.com/en-us/library/e4x1k99a.aspx
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
It would seem that the answer is No, its not possible. Just tried in Excel 2003 with text boxes or with a proper org chart on the sheet and a simple Find function could not find the required text.

No doubt somebody on here would be able to write code that would check the value of each individual text box on each sheet but I guess there might be too many variables such as number of text boxes on the sheet to make it viable.

Cheers
Rob H
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Something like this:
Sub FindName()
    Dim rngName As Range
    Dim wks As Worksheet
    Dim tb As TextBox
    ' this is the input cell - adjust as need
    Set rngName = Sheets("Sheet1").Range("A1")
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.TextBoxes
            If InStr(1, tb.Text, rngName.Value, vbTextCompare) > 0 Then
                wks.Activate
                Exit Sub
            End If
        Next tb
    Next wks
End Sub

Open in new window

Author

Commented:
When i set this macro up and run it i get this error message

Run time error "9"
Subscript out of range
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
See the comment in line 5 - you need to alter the sheet and range in line 6 as you didn't give any details in your post.

Author

Commented:
This is what i have and i am still getting the same error messages

Sub FindName()
    Dim rngName As Range
    Dim wks As Worksheet
    Dim tb As TextBox
    ' this is the input cell - adjust as need
    Set rngName = Sheets("Sheet1:Sheet3").Range("A1:Z100")
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.TextBoxes
            If InStr(1, tb.Text, rngName.Value, vbTextCompare) > 0 Then
                wks.Activate
                Exit Sub
            End If
        Next tb
    Next wks
End Sub
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
That makes no sense. Based on the original question the code assumes ONE input cell which is what you should specify and then tries to find the name in that cell in the textboxes on the other sheets. If it finds it, it will activate that sheet and then exit.

Author

Commented:

so this is where you type in what you want to find?

Set rngName = Sheets("Sheet1:Sheet3").Range("A1:Z100")
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Made these changes and the macro worked.  THANKS!!!!!!

Sub FindName()
    Dim rngName As Range
    Dim wks As Worksheet
    Dim tb As TextBox
    ' this is the input cell - adjust as need
    Set rngName = Sheets("Sheet1").Range("A15")
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.TextBoxes
            If InStr(1, tb.Text, rngName.Value, vbTextCompare) > 0 Then
                wks.Activate
                Exit Sub
            End If
        Next tb
    Next wks
End Sub
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.