Input box...

**meet2mayank** · 02-24-2010, 03:50 AM

I need a code for input box asking "Please enter Business Unit".
It should except only 4 digits which should be numeric. Should not accept text or less or more than 4 digits. If tried to enter should give an error.
It should accept only these business units - 0513, 0517, 0527, 2155, 2159, 2127, 3654 & 3655.
If user try to input any other thing apart from the above business units it should show error as "INVALID ENTRY".
The inputted data should get stored in sheet named "Control Sheet" in "C3"

Second input box should be asking for "Please enter date in mm-dd-yyyy format"
if the input is not in this format it should give an error "INVALID ENTRY".
If properly inputted the same should get stored in sheet named"Control Sheet" in "K3"

Kindly Help

--
Thanks,

Mayank

**tony h** · 02-24-2010, 04:05 AM

Hi,
did you realise tha you can do this on the spreadsheet without code? You can use the Data Validation (Data tab ... data vlidation) on the cell.

for the first item use List and then either have a list on the spreadsheet or put in the values seperated by commas. For the second item use Date.

Hope this helps

**meet2mayank** · 02-24-2010, 04:16 AM

Hi Tony....

My requirement is just a small part of my whole macro which is almost ready. Though you are right in saying that it can be done through data validation, but I need this do be done through a macro, as this would be a part of my whole macro to be done in a single click.

Thanks
Mayank

**buran** · 02-24-2010, 04:31 AM

Hi,
have you thinked about using userform with a combo box for the business units and a textbox for the date?

Buran

**meet2mayank** · 02-24-2010, 05:40 AM

Hi Buran,
Definately I would be using userform for the same. But I need a macro to link that userform button with macro which will perform the above activity.

Thanks.
Mayank

**buran** · 02-24-2010, 06:10 AM

If you are using user form as I suggested, then you can add to the combo box only these unit numbers that you want and prevent user from entering something different (MatchRequired Property). So you don't have to check this.
Regarding the date, you can use for example exit event to check if entry is a valid date:

Please Login or Register  to view this content.

Of course, your form will have at least two Command Buttons - OK and Cancel and you will set Enable property of the OK button to false and change it to True only when both the combobox and text box entries are valid.