Macro Data Input Message

[anovice]

Hi
I trawled the help sites and found this code to switch Data Input Messages on and off. I need this function depending on the experience of the user.
It works fine although it does take up to 60 secs to complete which is probably ok as there are 1000 rows and 15 columns to switch on and off.
The problem is when I try to 'Exit' or 'Save' excel crashes and closes leaving a file on the desktop A83D 1000.

Any ideas would be appreciated.

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

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

[mdmackillop]

Can you post a small sample to demonstrate?

[anovice]

Hope this works!

Thanks

[Paul_Hossler]

Never mind -- Your example has different types of validation rules. My suggstion wouldn't work

Sorry 'bout that



Paul

[shrivallabha]

Cut the code from Module1 and paste it to ThisWorkbook and see if it works.

[anovice]

Thanks for your input Shrivallabha but that didn't work.

[mikerickson]

What happens if you remove the On Error Resume Next, it might be masking an error.
Perhaps matching On Error Goto 0's are in order.

[anovice]

Thanks mikerickson but still crashing.

[shrivallabha]

It has error 1004 which is suppressed by
On Error Resume Next
But it runs fine to begin with, it is only when you proceed for the next action Excel crashes.