Cell.Validation.ShowInput off after cell change

[Zoticos]

I used the macro below to turn off the data validation input message caption for the entire worksheet.

Now I was looking for a way to turn off message caption after a single cell value has been selected from the data validation drop down list.

I got it to work with the sendkey command:
Application.EnableCancelKey = xlDisabled
Application.SendKeys "{ESCAPE 2}", True

But it does take 1 1/2 sec. to remove the message caption. So I was looking for a faster VBA solution using perhaps the Cell.Validation.ShowInput command.


Thanks for your help!



Macro1:

Private Sub CellCaptionOFF_Click()
'Turn cell cation input message off
Dim Cell As Range
Dim ValObj As Validation

For Each Cell In ActiveSheet.UsedRange
Set ValObj = Cell.Validation
On Error Resume Next
ValObj.ShowInput = False
ValObj.ShowError = False
If Err = 1004 Then Err.Clear
On Error GoTo 0
Next Cell

Unload Me

End Sub


Macro2:

Private Sub CellCaptionON_Click()
'Turn cell cation input message on
Dim Cell As Range
Dim ValObj As Validation

For Each Cell In ActiveSheet.UsedRange
Set ValObj = Cell.Validation
On Error Resume Next
ValObj.ShowInput = True
ValObj.ShowError = True
If Err = 1004 Then Err.Clear
On Error GoTo 0
Next Cell

Unload Me
End Sub