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

[StargateFan]

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

 

[Michael Malinsky]

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.

 

[idyllicabyss]

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

 

[Bob Phillips]

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)