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

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

Change default in input box from "20" to "000000"? Have macro con
 

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

Change default in input box from "20" to "000000"? Have macro con
 

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

Change default in input box from "20" to "000000"? Have macro con
 

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

Change default in input box from "20" to "000000"? Have macro con
 

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

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

On Fri, 20 Oct 2006 15:47:37 -0400, "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

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

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

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

--
Regards,
Tom Ogilvy


"StargateFan" wrote in message
...
On Fri, 20 Oct 2006 15:47:37 -0400, "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

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

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

On Sun, 22 Oct 2006 13:42:38 -0400, "Tom Ogilvy"
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" wrote in message
.. .
On Fri, 20 Oct 2006 15:47:37 -0400, "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

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