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
|