We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel Form Control Button

barnescs
barnescs asked
on
Medium Priority
242 Views
Last Modified: 2012-06-21
I have an Excel workbook with several tabs.  Each tab contains an org chart.  The names on the org charts are in a text box.  I have this VBA code to perform a find on the workbook but once it finds the first person with either the entered last or first name it stops searching.  How to I alter my VBA code so that it finds all the people with the name, not just the first one?

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("Summary").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
Comment
Watch Question

>>>> How to I alter my VBA code so that it finds all the people with the name, not just the first one?

The line in your code "Exit Sub" stop the macro after the first find. If you remove that then the cde will find the next instance.

Sid

Author

Commented:
what about the programming after "Exit Sub"?  do i need to delete it also?
No rest remain the same in the above code except "wks.Activate". In it's place extra code goes to do what you want to do with the found text.

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
What exactly do you want to happen? If you remove the exit sub, it will just activate each sheet in turn and I don't see the point of that really?

Author

Commented:
I want it to find the first person with that name and if that is not the person that you are looking for then go to the next one until you either find who you are looking for or you reach the last tab in the workbook.
Like I mentioned in ID: 35469867, you have to replace the "wks.Activate" with the relevant code. Do you have the code or do you want us to write it for you? :)

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
What do you want to do when you find that name?

Sid

Author

Commented:
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("Summary").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
                If Msgbox("Found: " & tb.text & ". Continue search?", vbyesno) = vbno then Exit Sub
            End If
        Next tb
    Next wks
End Sub

This works PERFECT!!!!  THANKS!!!!!  :-)  :-)
Thank you :)

Sid

Author

Commented:
:-)

Author

Commented:
Thanks for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*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.