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

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

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 he

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

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 he

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

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