How do we request number like "1" (1, 2 or 3 ...) in an input or msg box?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFan - 25 Mar 2006 17:38 GMT

I have a log that has a user input box for a start DATE. I'd like to
do the same thing for the start DAY NUMBER.

I tried modifying a date macro to input this number, but no luck so
far with any attempts. The macro below should request a number
starting from 1 onwards (1, 2, 3, 4 ... etc.) and then put that number
into cell B4. This didn't work below (naturally <sigh>) <g>:
************************************************************************
Sub RequestStartDayNumber()
'
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a Day start number" & vbCrLf & _
"(i.e., ''1'' for display of ''Day 1'').", _
Title:="Day Start Number", _
Default:=Format(Date, "0"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
With Range("B4")
.NumberFormat = "0"
.Value = CDate(vResponse)
End With

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

************************************************************************

Thanks. :oD

Reply to this Message

Michael Malinsky - 25 Mar 2006 18:44 GMT

The problem is the IsDate function. IsDate is used to determine if a
value can be converted to a date. The expression of IsDate(expression)
must be either a date expression or a string expression (something that
looks like a date, such as 1/1/06 or January 1, 2006).

Your problem is that you are asking for the user to enter a single
number. Your module is evaluating IsDate("1") as false because the
string value "1" does not resemble a date (assuming the user entered
1).

If you post exactly what you are trying to accomplish maybe I or
someone else could be more helpful, but if you are just trying to
populate B4 with the value entered by the user, then you would have to
modify your Do...Loop.

Reply to this Message

idyllicabyss@googlemail.com - 25 Mar 2006 18:45 GMT

You're still checking that the user input is a date not just a number.

replace the line
Loop Until IsDate(vResponse)

with
loop until vResponse > 0 and VResponse < 32

Or whatever number range you need.

and replacee the line
.Value = CDate(vResponse)

with
.Value = vResponse

to stop trying to change the number using CDate

Reply to this Message

Bob Phillips - 25 Mar 2006 19:05 GMT

Perhaps this?

Sub RequestStartDayNumber()
'
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a Day start number" & vbCrLf & _
"(i.e., ''1'' for display of ''Day 1'').", _
Title:="Day Start Number", _
Default:=Day(Date), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until vResponse <> 0 And vResponse < 32
With Range("B4")
.NumberFormat = "0"
.Value = vResponse
End With

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I have a log that has a user input box for a start DATE. I'd like to
> do the same thing for the start DAY NUMBER.
[quoted text clipped - 32 lines]
>
> Thanks. :oD

Reply to this Message