Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with an InputBox
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with an InputBox
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with an InputBox
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. "Jim Cone" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with an InputBox
I think you'll find that your code can and will ignore any data|validation rules
you set. TomD wrote: 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. "Jim Cone" wrote: 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" 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with an InputBox
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 "Dave Peterson" wrote: I think you'll find that your code can and will ignore any data|validation rules you set. TomD wrote: 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. "Jim Cone" wrote: 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" 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |