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

--
Regards,
Tom Ogilvy

"Phil" wrote:

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

[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" wrote:

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

[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 wrote:
> I really appreciate the help(worked perfectly), Tom. I hope
> eventually I can contribute meaningfully to this group.

[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



Phil wrote:
> 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 wrote:
> > I really appreciate the help(worked perfectly), Tom. I hope
> > eventually I can contribute meaningfully to this group.