Data from Input Box overrides cell input validation

Hello Tjaart,

Can you post the InputBox code portion?

Thanks,
Leith

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.

If Sheet1.Range("M13") = "" Then
Application.DisplayAlerts = True
Range("M13").Validation.IgnoreBlank = True
Dim Message, Title, Default, MyValue
Message = "                                     Please enter your name"
Title = "                                              Welcome"
Default = ""
MyValue = InputBox(Message, Title, Default)
Range("M13").Value = 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)
Range("H17").Value = MyValue
End If

Regards

Tjaart

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.