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
Dim msg as String
msg = "Enter the pertinent requisition number."
Do
vResponse = Application.InputBox( _
Prompt:=msg, _
Title:="Requisition Number", _
Default:=Day(Date), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
bQuit = False
if isnumeric(vResponse) then
If clng(vResponse) 0 And _
clng(vResponse) < 1000000 then
bQuit = True
end if
end if
msg = "Bad entry Dude, Try again!"
Loop Until bQuit
With Range("B2")
.NumberFormat = "000000"
.Value = vResponse
.Parent.Name = .Text
End With

ActiveSheet.Protect ' place at end of code
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"StargateFanFromWork" wrote:

So far, the code below is what I have. A new sheet based on a hidden
template sheet is brought forward with this macro and then the user is
requested for the pertinent requisition number to get dumped into cell B2:

----------------------------------------------------------------------------
--------------------------------
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 the pertinent requisition number.", _
Title:="Requisition Number", _
Default:=Day(Date), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until vResponse < 0 And vResponse < 1000000
With Range("B2")
.NumberFormat = "0"
.Value = vResponse
End With

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

There are 2 things that I can't seem to achieve.

1) In the user input box, the default value is "20". Would it be possible
to get something like "000000", instead? The req numbers are all 6 digits
long.

2) Then once the user returns the req #, can the macro then go on to name
the sheet tab by that same very same number?

Hoping this isn't too hard. I've been here for nearly an hour trying to get
this to work, myself, but the above code is all I've managed.

Thanks. :oD