Having some difficulties figuring out how to change some default
text. After a few minutes, figured out that "default text" would
bring up better hits in googling this group but nothing comes up that
I can work with since I am rather limited in my vb abilities.

I have this script that was a kind gift a couple of years ago or so:
----------------------------------------
Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Before:=Worksheets(1)
Worksheets(1).Visible = xlSheetVisible
ActiveSheet.Unprotect 'place at the beginning of the code

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a name for the new tab.", _
Title:="Tab Name", _
Default:=Format(Day(Date), "000000"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until vResponse <> 0 And vResponse < 1000000
With Range("A2")
.NumberFormat = "000000"
.Value = vResponse
End With

On Error GoTo CanNotRename
ActiveSheet.Name = Format(vResponse, "000000")
On Error GoTo 0
Exit Sub

'Error code
CanNotRename:
MsgBox "Can't rename sheet to " & Format(vResponse, "000000")
Resume Next

ActiveSheet.Protect ' place at end of code
End Sub
----------------------------------------

It allows me to "start" a new sheet and then name it right then and
there. But the naming convention was a number. I need text with the
default text being, say, "Tab ", just to give an example

How can I change the above to reflect a new format for the tab name,
pls?

Thanks! D

Hi,

Maybe something like this

Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Before:=Worksheets(1)
Worksheets(1).Visible = xlSheetVisible
ActiveSheet.Unprotect 'place at the beginning of the code

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a name for the new tab.", _
Title:="Tab Name", _
Default:="Tab", _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until vResponse <> ""
With Range("A2")
.Value = vResponse
End With

On Error GoTo CanNotRename
ActiveSheet.Name = Format(vResponse, "000000")
On Error GoTo 0
Exit Sub

'Error code
CanNotRename:
MsgBox "Can't rename sheet to " & vResponse
Resume Next

ActiveSheet.Protect ' place at end of code
End Sub


Mike

"(E-Mail Removed)" wrote:

> Having some difficulties figuring out how to change some default
> text. After a few minutes, figured out that "default text" would
> bring up better hits in googling this group but nothing comes up that
> I can work with since I am rather limited in my vb abilities.
>
> I have this script that was a kind gift a couple of years ago or so:
> ----------------------------------------
> Sub NewSheet_Add()
> Worksheets("TEMPLATE").Copy Before:=Worksheets(1)
> Worksheets(1).Visible = xlSheetVisible
> ActiveSheet.Unprotect 'place at the beginning of the code
>
> Dim vResponse As Variant
> Do
> vResponse = Application.InputBox( _
> Prompt:="Enter a name for the new tab.", _
> Title:="Tab Name", _
> Default:=Format(Day(Date), "000000"), _
> Type:=2)
> If vResponse = False Then Exit Sub 'User cancelled
> Loop Until vResponse <> 0 And vResponse < 1000000
> With Range("A2")
> .NumberFormat = "000000"
> .Value = vResponse
> End With
>
> On Error GoTo CanNotRename
> ActiveSheet.Name = Format(vResponse, "000000")
> On Error GoTo 0
> Exit Sub
>
> 'Error code
> CanNotRename:
> MsgBox "Can't rename sheet to " & Format(vResponse, "000000")
> Resume Next
>
> ActiveSheet.Protect ' place at end of code
> End Sub
> ----------------------------------------
>
> It allows me to "start" a new sheet and then name it right then and
> there. But the naming convention was a number. I need text with the
> default text being, say, "Tab ", just to give an example
>
> How can I change the above to reflect a new format for the tab name,
> pls?
>
> Thanks! D
>