Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab?

[StargateFanFromWork]

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 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 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. D

 

[Charles Chickering]

oops forgot part2
ActiveSheet.Name = vResponse

 

[Charles Chickering]

You already set it to 20 Day(Date) = the today the 20th just set the
default to "000000"

 

[Jim Thomlinson]

How about this...

vResponse = Application.InputBox( _
Prompt:="Enter the pertinent requisition number.", _
Title:="Requisition Number", _
Default:=format(Day(Date), "000000"), _
Type:=2)

To get the proper format of the default. To rename the sheet...

on error goto CanNotRename
acitvesheet.name = format(vResponse, "000000")
on error goto 0

'Some more code

Exit Sub
CanNotRename:
msgbox "Can't rename sheet to " & format(vResponse, "000000")
resume next
end sub

 

[Tom Ogilvy]

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

 

[StargateFan]

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 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 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. D

Is there a way to do these two things? Tx.

 

[Tom Ogilvy]

I don't know. Are you rejecting the suggestions you have already received.

 

[StargateFan]

I don't know. Are you rejecting the suggestions you have already received.

Oh, boy, that happens sometimes, doesn't it ... I'll have to go to the
archives to see what answers I've recvd that didn't show up here in
Agent. I didn't recv any responses so something happened.

Thanks for letting me know.

 

[StargateFanFromWork]

Just went to the archives and found the missing 3 responses that I never
recvd. Thanks to everyone who responded.

I fixed the format of the Day/Date, as recommended. I didn't realize that
part, that it was actually a date format. I just took that code from
another working spreadsheet. Good thing that with the modifications, it
doesn't matter that it's a date format, as this accomplishes a number format
now very well.

Then I incorporated the part that would rename the tab according to the
user's input, so now it works beautifully. The code now looks like this
below; I don't guarantee that it's the best code, as it's a newbie who's put
it together like some badly-patched Frankenstein from bits and pieces here
and there, but it's working!:

****************************************************************************
**************
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 the pertinent requisition number.", _
Title:="Requisition Number", _
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
****************************************************************************
**************

Thanks very, very much once again! You guys make me work much more
efficiently!

D