Like operator
 

I am trying to write vba code so a user can enter a worksheet name using an
inputbox. The worksheets are named by the client name. e.g. "Smith, Adam" and
I would like the user to be able to put in the name "Smith" and have the
correct worksheet selected, or a range of worksheets if there are more than 1
"Smith". How can I write this. The code I have written so far is:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String

Enterclname:

stclname = InputBox("Enter the Client Name")
Worksheets(stclname).Activate

Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname


End Sub

Thanks in advance

Like operator
 

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String
Dim sh As Worksheet

Enterclname:

stclname = InputBox("Enter the Client Name")
For Each sh In ThisWorkbook.Worksheets
If stclname Like "*" & sh.Name & "*" Then
sh.Activate
GoTo Exit_Workbook_Open
End If
Next sh

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname

Exit_Workbook_Open:

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"marianne" wrote in message
...
I am trying to write vba code so a user can enter a worksheet name using
an
inputbox. The worksheets are named by the client name. e.g. "Smith, Adam"
and
I would like the user to be able to put in the name "Smith" and have the
correct worksheet selected, or a range of worksheets if there are more
than 1
"Smith". How can I write this. The code I have written so far is:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String

Enterclname:

stclname = InputBox("Enter the Client Name")
Worksheets(stclname).Activate

Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname


End Sub

Thanks in advance

Like operator
 

Hello Marianne,

Please find my solution (see the code below). I modiffied a little bit
your code to open an appropriate array of sheet names.
Appropriate - means that the given pattern is searched in a set of
workbook names (function FindAllNames) .

Option Explicit

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stClName As String
Dim stermes As String
Dim namesArr()


EnterClName:

stClName = InputBox("Enter the Client Name") 'it's the pattern
namesArr = FindAllNames(stClName)
Worksheets(namesArr).Select
Worksheets(namesArr(0)).Activate
Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("The pattern wasn't found. Check the spelling")
Resume EnterClName

End Sub

'search for the given pattern
Private Function FindAllNames(stClName As String)
Dim sheetNo As Integer
Dim RegEx
Dim iAllNames As Integer
Dim AllNames()
iAllNames = 0

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.IgnoreCase = True
RegEx.Pattern = stClName

For sheetNo = 1 To Worksheets.Count
If RegEx.test(Worksheets(sheetNo).Name) Then
ReDim Preserve AllNames(iAllNames)
AllNames(iAllNames) = Worksheets(sheetNo).Name
iAllNames = iAllNames + 1
End If
Next

If iAllNames < 0 Then
FindAllNames = AllNames
End If
End Function

Best Regards,

Agnieszka

Like operator
 

I have home-made XLA that should help: http://cjoint.com/?fwqZQdNgus
It is not protected: you can view & edit the code and the userform

To have tabs alpha ordered, right click on arrows left of tabs list, and
select "Sort tabs"

To select a sheet:
Right-click on a cell and select "Select sheet"
You can type the beginning of the name: it will select corresponding entry
of the list.
To validate: press Enter or double-click on a name
To cancel: press Escape

HTH
--
AP

"marianne" a écrit dans le message de
news: ...
I am trying to write vba code so a user can enter a worksheet name using an
inputbox. The worksheets are named by the client name. e.g. "Smith, Adam"
and
I would like the user to be able to put in the name "Smith" and have the
correct worksheet selected, or a range of worksheets if there are more
than 1
"Smith". How can I write this. The code I have written so far is:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String

Enterclname:

stclname = InputBox("Enter the Client Name")
Worksheets(stclname).Activate

Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname


End Sub

Thanks in advance

Like operator
 

Thank you for the prompt response
I tried the code but I get an error
Compile error
Sub or Function not defined:
and it highlights "FindAllNames"


" wrote:

Hello Marianne,

Please find my solution (see the code below). I modiffied a little bit
your code to open an appropriate array of sheet names.
Appropriate - means that the given pattern is searched in a set of
workbook names (function FindAllNames) .

Option Explicit

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stClName As String
Dim stermes As String
Dim namesArr()


EnterClName:

stClName = InputBox("Enter the Client Name") 'it's the pattern
namesArr = FindAllNames(stClName)
Worksheets(namesArr).Select
Worksheets(namesArr(0)).Activate
Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("The pattern wasn't found. Check the spelling")
Resume EnterClName

End Sub

'search for the given pattern
Private Function FindAllNames(stClName As String)
Dim sheetNo As Integer
Dim RegEx
Dim iAllNames As Integer
Dim AllNames()
iAllNames = 0

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.IgnoreCase = True
RegEx.Pattern = stClName

For sheetNo = 1 To Worksheets.Count
If RegEx.test(Worksheets(sheetNo).Name) Then
ReDim Preserve AllNames(iAllNames)
AllNames(iAllNames) = Worksheets(sheetNo).Name
iAllNames = iAllNames + 1
End If
Next

If iAllNames < 0 Then
FindAllNames = AllNames
End If
End Function

Best Regards,

Agnieszka

Like operator
 

Thank you for your prompt reply

I have tried your code but it doens't seem to work.
I get the error message twice and then the Inputbox comes up again.

Marianne


"Bob Phillips" wrote:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String
Dim sh As Worksheet

Enterclname:

stclname = InputBox("Enter the Client Name")
For Each sh In ThisWorkbook.Worksheets
If stclname Like "*" & sh.Name & "*" Then
sh.Activate
GoTo Exit_Workbook_Open
End If
Next sh

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname

Exit_Workbook_Open:

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"marianne" wrote in message
...
I am trying to write vba code so a user can enter a worksheet name using
an
inputbox. The worksheets are named by the client name. e.g. "Smith, Adam"
and
I would like the user to be able to put in the name "Smith" and have the
correct worksheet selected, or a range of worksheets if there are more
than 1
"Smith". How can I write this. The code I have written so far is:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String

Enterclname:

stclname = InputBox("Enter the Client Name")
Worksheets(stclname).Activate

Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname


End Sub

Thanks in advance

Like operator
 

Hi Marianne,

You should try to use whole code (not only the first procedure).
Here you have the part defining the FindAllNames function (which was
also given before).

'search for the given pattern
Private Function FindAllNames(stClName As String)
Dim sheetNo As Integer
Dim RegEx
Dim iAllNames As Integer
Dim AllNames()
iAllNames = 0

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.IgnoreCase = True
RegEx.Pattern = stClName

For sheetNo = 1 To Worksheets.Count
If RegEx.test(Worksheets(sheetNo).Name) Then
ReDim Preserve AllNames(iAllNames)
AllNames(iAllNames) = Worksheets(sheetNo).Name
iAllNames = iAllNames + 1
End If
Next

If iAllNames < 0 Then
FindAllNames = AllNames
End If
End Function

Good luck,

Aga

marianne napisal(a):
Thank you for the prompt response
I tried the code but I get an error
Compile error
Sub or Function not defined:
and it highlights "FindAllNames"


" wrote:

Hello Marianne,

Please find my solution (see the code below). I modiffied a little bit
your code to open an appropriate array of sheet names.
Appropriate - means that the given pattern is searched in a set of
workbook names (function FindAllNames) .

Option Explicit

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stClName As String
Dim stermes As String
Dim namesArr()


EnterClName:

stClName = InputBox("Enter the Client Name") 'it's the pattern
namesArr = FindAllNames(stClName)
Worksheets(namesArr).Select
Worksheets(namesArr(0)).Activate
Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("The pattern wasn't found. Check the spelling")
Resume EnterClName

End Sub

Best Regards,

Agnieszka