Change default number to default text?
 

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 Befo=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! :oD

Change default number to default text?
 

Hi,

Maybe something like this

Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Befo=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

" 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 Befo=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! :oD