Compare Worksheet Names - Truncate?

[Trader_in_Paradise]

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?

 

[Vergel Adriano]

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

 

[Trader_in_Paradise]

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.

 

[Vergel Adriano]

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 procedure:

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.

 

[Trader_in_Paradise]

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 procedure:

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.

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