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

oops forgot part2
ActiveSheet.Name = vResponse
--
Charles Chickering

"A good example is twice the value of good advice."


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

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

"A good example is twice the value of good advice."


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

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

Jim Thomlinson


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

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

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

On Fri, 20 Oct 2006 15:47:37 -0400, "StargateFanFromWork"
<(E-Mail Removed)> 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 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.

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

--
Regards,
Tom Ogilvy


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:(E-Mail Removed)...
> On Fri, 20 Oct 2006 15:47:37 -0400, "StargateFanFromWork"
> <(E-Mail Removed)> 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 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.
>

On Sun, 22 Oct 2006 13:42:38 -0400, "Tom Ogilvy" <(E-Mail Removed)>
wrote:

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

>--
>Regards,
>Tom Ogilvy
>
>
>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>news:(E-Mail Removed)...
>> On Fri, 20 Oct 2006 15:47:37 -0400, "StargateFanFromWork"
>> <(E-Mail Removed)> 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 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.
>>
>

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



"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:(E-Mail Removed)...
> On Sun, 22 Oct 2006 13:42:38 -0400, "Tom Ogilvy" <(E-Mail Removed)>
> wrote:
>
> >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.
>
> >--
> >Regards,
> >Tom Ogilvy
> >
> >
> >"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> >news:(E-Mail Removed)...
> >> On Fri, 20 Oct 2006 15:47:37 -0400, "StargateFanFromWork"
> >> <(E-Mail Removed)> 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 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.