Data from Input Box overrides cell input validation

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:

Sub EnterData(Input_Cell As Range, Input_Data As String)

  With Input_Cell
    .Value = ""
    .Select
  End With

   'Place the Cursor in the Formula Bar
    SendKeys "{F2}"

   'Put the data in the Formula and Enter it
    SendKeys Input_Data & "{Enter}"
  
End Sub

Macro added to your code:

  Dim Message, Title, Default, MyValue    
    If Sheet1.Range("M13") = "" Then
       Application.DisplayAlerts = True
       Range("M13").Validation.IgnoreBlank = True
         Message = "Please enter your name"
         Title = "Welcome"
         Default = ""
           MyValue = InputBox(Message, Title, Default)
         EnterData Sheet1.Range("M13"), MyValue
    End If

    If Range("M13") <> "" And Range("H17") = 0 Then
       Message = "Please enter any number"
       Title = "Next step"
       Default = "0"
         MyValue = InputBox(Message, Title, Default, 1850, 1875)
       EnterData Sheet1.Range("H17").Value, MyValue
    End If

Sincerely,
Leith Ross

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

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

MyValue = Application.InputBox(Message, Title, Default)

will not allow data validation

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

MyValue = Application.InputBox(Message, Title, Default, Type:=1)

will only accept numerical entries

MyValue = Application.InputBox(Message, Title, Default, Type:=2)

will only accept text.

Hello Tjaart,

Glad to help out, and thanks for the feedback.

Sincerely,
Leith Ross

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

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

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