Data from Input Box overrides cell input validation

**Winon** · 05-19-2007, 03:20 AM

I have two cells on a worksheet whith a custom validation for cell "M13" permitting Istext only and for cell "H17" permitting Isnumber only. To make it easier for the user, I have created an Input Box, but regardless of what is entered in this box the cell/s accept/s and ignore/s any validation.

I found some code in the 'On-Screen Help' to Add or Modify xlValidationCustom etc., but it does not work, no matter how hard I try to tweak and twist.

Could someone please help me with the correct code to maintain validations for the respective cells via the Input Box, to include an error message "You must enter a number" or "Text only is allowed, and no numbers"?

If it is of any help, I am still working with Office2000.

Thanks Guys!

**Leith Ross** · 05-19-2007, 03:30 AM

Hello Tjaart,

Can you post the InputBox code portion?

Thanks,
Leith

**Winon** · 05-19-2007, 04:16 AM

Hello Leith,

And once again it is Leith to the rescue!

Thank you.

I have taken out the xlValidateCustom etc. since it does not work, and I am sending you what I have got in terms of the InputBox.

Please Login or Register  to view this content.

Regards

Tjaart

**Leith Ross** · 05-20-2007, 03:51 PM

Hello Tjaart,

Data Validation only occurs when Excel is in edit mode. The user is either typing in the Formula Bar or changing the cells contents directly. Changing a cell's contents using VBA bypasses the Validation, as you discovered. The trick is how do we trigger the Validation when using VBA code?

The obvious answer is type something into the formula. With VBA, we can do just that. Excel has a hot Key "F2" that places the cursor in the Formula Bar. Next we need to transfer the string from the InputBox to the Formula Bar along with the Enter key. we can accomplish all this with the VBA SendKeys statement. Copy the macro and paste it into a Standard VBA Module. It is highlighted in Blue in your code.

Macro code:

Please Login or Register  to view this content.

Macro added to your code:

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Winon** · 05-23-2007, 03:20 PM

Hello Leith,

Thank you for your help. You make it look so simple and yet, I would not have been able to figure it out for myself!

I am sure that other forum viewers must have learnt from your solution as well.

Keep up the good work! I may call on you again.

Kind regards,

Tjaart

**mikerickson** · 05-23-2007, 03:51 PM

There are two kinds of input boxes. The one you use (the function)

Please Login or Register  to view this content.

will not allow data validation

The version Application.InputBox (method) allows you to specify which kind of data it will accept.

Please Login or Register  to view this content.

will only accept numerical entries

Please Login or Register  to view this content.

will only accept text.

**Leith Ross** · 05-23-2007, 05:02 PM

Hello Tjaart,

Glad to help out, and thanks for the feedback.

Sincerely,
Leith Ross

**Winon** · 05-27-2007, 09:35 AM

Hello mikerickson,

Thanks for your feedback. I shall give it a try, and let you know. What I have done in the meantime with the code-fix from Leith Ross, was to tweak a little by adding dim MyValue as String, and when the message box appears to have the the cell to which the validation applies activated, and it will accept either text or integers only.

Whether it is right or wrong, I don't know, but it works. I am relatively very new to VBA and still have a long way to go. The first time I tried these adjustments, the response from the system was to open the VBA Object Browser, I closed it and it worked. No problem, even if I shut the system down and start up again, it works fine!

Was this by pure accident, or am I setting myself up for disaster?

What do you think Leith?

Anybody else?

Take care guys,

Tjaart

**Winon** · 06-01-2007, 01:35 PM

Hi to you all,

Its been a while since my last posting. Any comments from anyone on my last posting?

Or should I not worry?

Cheers

**Leith Ross** · 06-01-2007, 01:42 PM

Hello Tjaart,

I am not sure what your question is. Can you give me some examples of the problem(s)? Whose code did you use?

Thanks,
Leith Ross

**Winon** · 06-01-2007, 02:29 PM

Hello Leith,

Haven't seen you on the forum for a while.

If you follow this thread from where Mikerickson replied with some comments, you will see what it is about. I used the code with the appropriate corrections you had sent me.

Initially it gave my some "Compile error.Byref argument mismatch" message

Regards

Tjaart