Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Searching for names in Excel workbook

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?
0
barnescs
Asked:
barnescs
1 Solution
 
cjrmail2kCommented:
0
 
barnescsAuthor 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?
0
 
GkCommented:
Search function helps you.
If you want by VB, refer this page:
http://msdn.microsoft.com/en-us/library/e4x1k99a.aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rob HensonIT & Database AssistantCommented:
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
0
 
Rory ArchibaldCommented:
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

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

Run time error "9"
Subscript out of range
0
 
Rory ArchibaldCommented:
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.
0
 
barnescsAuthor 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
0
 
Rory ArchibaldCommented:
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.
0
 
barnescsAuthor Commented:

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

Set rngName = Sheets("Sheet1:Sheet3").Range("A1:Z100")
0
 
Rory ArchibaldCommented:
The original version assumed you were entering a name into A1 on Sheet1. It would then search for the name and activate that sheet. Was that what you wanted?
0
 
barnescsAuthor 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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now