Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Worksheet Names - Truncate?
Private Sub btnRenameE_Click()
Dim MyName As String Dim MySheet As Object Set MySheet = ActiveWorkbook.ActiveSheet Dim Sheet As Object On Error GoTo btnRename_Err: NamingRoutine: ' Input box to capture User's Input for Worksheet Name MyName = Application.InputBox("What would you like to name this Sheet?", "Your Name for this Sheet") ' Now test for reserved names, Options, Dates or Scratch ElseIf MyName = "Options" Or MyName = "Dates" Or MyName = "Scratch" Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: This Code Fails if MyName contains the string "Options" or "Date" or "Scratch" For Example, if MyName = someDate or Options1, the code loops to NamingRoutine. Is there a truncate or exact function? Any other suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Worksheet Names - Truncate?
Trader,
If MyName=someDate or MyName = Options1, the code should ask for a new name, right? Try something like this: If InStr(1, MyName, "Options", vbTextCompare) 0 Or _ InStr(1, MyName, "Dates", vbTextCompare) 0 Or _ InStr(1, MyName, "Scratch", vbTextCompare) 0 Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: End If -- Hope that helps. Vergel Adriano "Trader_in_Paradise" wrote: Private Sub btnRenameE_Click() Dim MyName As String Dim MySheet As Object Set MySheet = ActiveWorkbook.ActiveSheet Dim Sheet As Object On Error GoTo btnRename_Err: NamingRoutine: ' Input box to capture User's Input for Worksheet Name MyName = Application.InputBox("What would you like to name this Sheet?", "Your Name for this Sheet") ' Now test for reserved names, Options, Dates or Scratch ElseIf MyName = "Options" Or MyName = "Dates" Or MyName = "Scratch" Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: This Code Fails if MyName contains the string "Options" or "Date" or "Scratch" For Example, if MyName = someDate or Options1, the code loops to NamingRoutine. Is there a truncate or exact function? Any other suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Worksheet Names - Truncate?
"Vergel Adriano" wrote: Trader, If MyName=someDate or MyName = Options1, the code should ask for a new name, right? Try something like this: If InStr(1, MyName, "Options", vbTextCompare) 0 Or _ InStr(1, MyName, "Dates", vbTextCompare) 0 Or _ InStr(1, MyName, "Scratch", vbTextCompare) 0 Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: End If -- Hope that helps. Vergel Adriano I did not explain the problem correctly. I wish to reserve the exact names "Options", "Dates" and "Scratch" Any deviation from these names, except simply capitalization, is acceptable. For Example: OPTIONS is not acceptable, nor is dates or SCratch. However, Options1 or OptionsJun or DateMay are names that I would expect and these names are acceptable. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Worksheet Names - Truncate?
At the top of your code module, put this line
Option Compare Text What it does is, when you do string comparisons, the compare will not be case sensitive. So, if there are other parts in your code where "Dates" should not be the same as "DATES", then this will not work.. Otherwise, with the Option Compare Text line at the top of your code module, you can have something like this in your sub procedu If MyName = "Options" Or _ MyName = "Dates" Or _ MyName = "Scratch" Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: End If it will not allow "Options", "Dates", "Scratch" and any variations in case. But it will allow Options1 or OptionsJun or DateMay, etc. -- Hope that helps. Vergel Adriano "Trader_in_Paradise" wrote: "Vergel Adriano" wrote: Trader, If MyName=someDate or MyName = Options1, the code should ask for a new name, right? Try something like this: If InStr(1, MyName, "Options", vbTextCompare) 0 Or _ InStr(1, MyName, "Dates", vbTextCompare) 0 Or _ InStr(1, MyName, "Scratch", vbTextCompare) 0 Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: End If -- Hope that helps. Vergel Adriano I did not explain the problem correctly. I wish to reserve the exact names "Options", "Dates" and "Scratch" Any deviation from these names, except simply capitalization, is acceptable. For Example: OPTIONS is not acceptable, nor is dates or SCratch. However, Options1 or OptionsJun or DateMay are names that I would expect and these names are acceptable. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Worksheet Names - Truncate?
"Vergel Adriano" wrote: At the top of your code module, put this line Option Compare Text What it does is, when you do string comparisons, the compare will not be case sensitive. So, if there are other parts in your code where "Dates" should not be the same as "DATES", then this will not work.. Otherwise, with the Option Compare Text line at the top of your code module, you can have something like this in your sub procedu If MyName = "Options" Or _ MyName = "Dates" Or _ MyName = "Scratch" Then MsgBox "The names, Options, Dates and Scratch are in use. Please Choose another name for this Worksheet", , "This Name is Reserved." GoTo NamingRoutine: End If it will not allow "Options", "Dates", "Scratch" and any variations in case. But it will allow Options1 or OptionsJun or DateMay, etc. -- Hope that helps. Vergel Adriano Trader_in_Paradise replies: Thanks, Vergel That helps. I also had success with your InStr function AND Len function combined to test for Options, Dates, Scratch. The following line works: ElseIf Len(MyName) = 7 And InStr(1, MyName, "Options", vbTextCompare) 0 Thanks again for all your help. TIP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to compare two columns of Names | Excel Discussion (Misc queries) | |||
Compare Worksheet Names - Ignore Case? | Excel Programming | |||
Compare two lists of names | Excel Discussion (Misc queries) | |||
Excel Worksheet Subtotals Data Truncate | Excel Programming | |||
Compare names in two different worksheets | Excel Programming |