Search for worksheet name

[Phil]

I'm new to VBA, and so my question is probably trivial to most here:

I'm trying to find a sheet in workbook that best-matches the name the
user enters and then activates that sheet.


here's what i have so far: i get a "out of range error" when
activating the desired sheet.

I realize the my instr function will only give me the first instance,
but my real problem is activating the desired sheet.

Thanks!

-Phil

// ****** code below ******

Sub FindSheet()
Dim sh As Worksheet 'sh is var of worksheet class
Dim name As String

name = InputBox(prompt:=" enter desired worksheet to find ")

For Each sh In ActiveWorkbook.Sheets

If InStr(UCase(sh.name), UCase(name)) > 0 Then 'this is
instring function
Exit For
End If

Next sh

Sheets("name").Activate 'activeworkbook is READ ONLY
' make active sheet the sought one


End Sub

 

[Tom Ogilvy]

Sub FindSheet()
Dim sh As Worksheet
Dim sname As String

sname = InputBox(prompt:=" enter desired worksheet to find ")
if sname <> "" then
For Each sh In ActiveWorkbook.Sheets

If InStr(1,sh.name,sname,vbTextCompare) > 0 Then
sh.Activate
Exit Sub
End If

Next sh
Msgbox "Likely match to " & sName & " not found"
Else
Msgbox "No name entered"
end if
End Sub

 

[Michael]

Why don't you create a list of the sheets you have in the workbook and place
them in a dropdow box object for the user to select whatever they want.
Setting the event on change to select the sheet chose by the user?

 

[Phil]

I really appreciate the help(worked perfectly), Tom. I hope
eventually I can contribute meaningfully to this group.

 

[Phil]

Here's code that'll search for a phrase in a worksheet name, activate
the first sheet with the phrase in it, prompt the user if he wants that
sheet, if not, cycle through all worksheets with that phrase in it
until he finds one that he wants.

************************ start of subroutine ***********
Sub FindSheet()
Dim sh As Worksheet
Dim sname As String
Dim counter As Integer
Dim answer
Dim i As Integer
Dim sname2 As String

sname = InputBox(prompt:=" enter desired worksheet to find ")
If sname <> "" Then

counter = 0

For Each sh In ActiveWorkbook.Sheets
counter = counter + 1
If InStr(1, sh.name, sname, vbTextCompare) > 0 Then
sh.Activate
answer = MsgBox(" is this good? ", vbYesNo)
If answer = vbYes Then
Exit Sub
Else
'MsgBox "another"
For i = counter + 1 To ActiveWorkbook.Sheets.Count -
1
sname2 = ActiveWorkbook.Worksheets(i).name
If InStr(1, Sheets(i).name, sname, vbTextCompare)

0 Then

ActiveWorkbook.Worksheets(i).Activate
answer = MsgBox(" is this good? ", vbYesNo)
If answer = vbYes Then Exit Sub
End If
Next
End If
End If

Next sh
End If
End Sub

******************* end of subroutine ****************

 

[Phil]

I tried running this on a spreadsheet generated by an oracle report
and i get an error at the:

For Each sh In ActiveWorkbook.Sheets

line.

Any ideas on why this is?

I tried saving the newly-generated report in excel as an excel doc,
and it still didnt work. also, in the VB editor, i didn't see any
objects or anything associated with the report.

I appreciate any insight into this problem.

Thanks,

-Phil