Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Operator for Contains | Excel Worksheet Functions | |||
Operator | Excel Worksheet Functions | |||
IN operator | Excel Worksheet Functions | |||
Mod operator | Excel Programming | |||
Can the AND operator be of use here? | Excel Programming |