Remove Data validation Input messages

M

Mark

Thanks to all who read this, your time is important: I am stuck. I
have used DATA VALIDATION in several cells of my application. Several
of these cells use the INPUT MESSAGE of the DATA VALIDATION as a kind
of mini Help menu. I would like to be able to offer the user the
option of turning off ALL of these tips as they can become annoying
after a-while. I have tried the following approach:

(Only one cell here as an attempt to get it to work at all...)

InvoiceEntry.Range("C15").Validation.ShowInput=False

It doesn't work. I have been through the object browser trying
to get the heirarchy straight and it seems to me I should be able to
do this, alas I cannot. If any of you MVP types out there, or anyone
else for that matter, have any suggestions, I would greatly appreciate
it.

To restate, I want to retain the Data Validation of the cell,
but remove the Input Message. Thanks again, Mark...
 
D

Debra Dalgleish

To turn the messages off:

'===================
Sub InputMsgOff()
Dim ws As Worksheet
Dim rng As Range
Dim c As Range
Set ws = ActiveSheet
Set rng = ws.Cells.SpecialCells(xlCellTypeAllValidation)
For Each c In rng
On Error Resume Next
c.Validation.ShowInput = False
Next c
End Sub
'=========================

and turn turn them on:
'==========================
Sub InputMsgOn()
Dim ws As Worksheet
Dim rng As Range
Dim c As Range
Set ws = ActiveSheet
Set rng = ws.Cells.SpecialCells(xlCellTypeAllValidation)
For Each c In rng
On Error Resume Next
c.Validation.ShowInput = True
Next c
End Sub
'=============================
 
M

Mark

Wow, that was quick and painless. Using the SPECIAL CELLS collection
was just what the Doctor ordered. You saved me many hours of
aggrivation, I am sure. Thanks again for your rapid and 'Hit the nail
on the head' reply.

Mark
 
D

Debra Dalgleish

You're welcome!
Wow, that was quick and painless. Using the SPECIAL CELLS collection
was just what the Doctor ordered. You saved me many hours of
aggrivation, I am sure. Thanks again for your rapid and 'Hit the nail
on the head' reply.

Mark
Click to expand...
 
Top