Data Validation with an InputBox

[TomD]

I'm trying to input my data using an InputBox, and also use the data
validations in the cells. I'm trying to follow the following instructions
from the Help File:

===================================================
The InputBox method differs from the InputBox function in that it allows
selective validation of the user's input, and it can be used with Microsoft
Excel objects, error values, and formulas. Note that Application.InputBox
calls the InputBox method; InputBox with no object qualifier calls the
InputBox function.

This example prompts the user to select a cell on Sheet1. The example uses
the Type argument to ensure that the return value is a valid cell reference
(a Range object).

Worksheets("Sheet1").Activate
Set myCell = Application.InputBox(prompt:="Select a cell", Type:=8)
====================================================

My code:

' Get the Birthday
'
myCell = ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
On Error GoTo CleanUp
Set myCell = Application.InputBox(Prompt:="Enter The Birthday
MM/DD/YYYY", Type:=8)

====================================================

I have tried numerous variations, and all have failed. Any assistance would
be greatly appreciated.

TomD

 

[Jim Cone]

I believe you need to use the "other" input box. The one without the
"Application" prefix...
'---------------------------
Sub HappyBirthday()
Dim varDate As Variant
varDate = InputBox("Enter the birthday as MM/DD/YYYY", _
"Tom Wants to Know", Format$(Date, "mm/dd/yyyy"))
If Len(varDate) = 0 Then
Exit Sub
Else
'verify entry and do something with it.
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"TomD" <[email protected]>
wrote in message
I'm trying to input my data using an InputBox, and also use the data
validations in the cells. I'm trying to follow the following instructions
from the Help File:
-snip-
My code:
' Get the Birthday
'
myCell = ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
On Error GoTo CleanUp
Set myCell = Application.InputBox(Prompt:="Enter The Birthday
MM/DD/YYYY", Type:=8)

====================================================

I have tried numerous variations, and all have failed. Any assistance would
be greatly appreciated.

TomD

 

[TomD]

Hi Jim,

I know that I can validate the data programatically, but what I want VB to
do is honor the validation that already applies to the cell/range, and it
appears from the help data, for InputBox, that it will do that. I don't
like to duplicate what already exists, if I don't have to. And, that is what
one has to do if you use the InputBox function, instead of the InputBox
Application. But, thanks for the suggestion anyway.

Also, I realize that Type 8 may not be the right type, but I tried others
and got the same error.

 

[Dave Peterson]

I think you'll find that your code can and will ignore any data|validation rules
you set.

 

[TomD]

If that's true, it's a shame to allow one application to destroy another.
Whether the data is being input manually or programatically, the integrity of
the recieving documebt should be respected.

I hope that somebody has an answer for me.

At the same time, I thank you for your opinion.

Tom