Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Validation Input Messages
Someone from another forum kindly gave me this code to remove all validation
input messages from a worksheet but it doesn't work or throw any error messages. .................................................. .......................... Sub RemoveValidationInputMsgs() On Error GoTo None With ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation).Validation .InputTitle = Empty .InputMessage = Empty End With None: On Error GoTo 0 End Sub .................................................. .......................... Any help appreciated with this, thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Validation Input Messages
Hi,
It works for me. Note that as written it only works on the active sheet, do you have the sheet you want it to work on selected? Mike "Code Numpty" wrote: Someone from another forum kindly gave me this code to remove all validation input messages from a worksheet but it doesn't work or throw any error messages. .................................................. ......................... Sub RemoveValidationInputMsgs() On Error GoTo None With ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation).Validation .InputTitle = Empty .InputMessage = Empty End With None: On Error GoTo 0 End Sub .................................................. ......................... Any help appreciated with this, thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Validation Input Messages
Your code wouldn't throw any error messages due to use of the error handler,
try commenting On error resume next. If the code fails it is probably due to mixed validation types on the sheet or protection. If due to the mixed types it means looping each validation cell, unless you have some other way of identifying areas that contain same type of validation.. Sub NoDVinputMsg() Dim rng As Range, cel As Range Set rng = Nothing ' only if rng previously set On Error Resume Next Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation) If Not rng Is Nothing Then bDummy = rng.Validation.ShowInput If Err.Number = 0 Then ' all same type, no need to loop With rng.Validation .InputTitle = "" .InputMessage = "" End With Else On Error GoTo 0 For Each cel In rng With cel.Validation .InputTitle = "" .InputMessage = "" End With Next End If End If End Sub BTW, if you only want to deisable the input messages and not permanently remove them, just do ..ShowInput = False Regards, Peter T "Code Numpty" wrote in message ... Someone from another forum kindly gave me this code to remove all validation input messages from a worksheet but it doesn't work or throw any error messages. .................................................. .......................... Sub RemoveValidationInputMsgs() On Error GoTo None With ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation).Validation .InputTitle = Empty .InputMessage = Empty End With None: On Error GoTo 0 End Sub .................................................. .......................... Any help appreciated with this, thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Validation Input Messages
Thanks Peter, I commented out On Error GoTo None and On Error GoTo 0 and got
error code 1004 and the debugger highlighted .InputTitle = Empty. So, I used your code which of course works like a dream, thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation: - different messages for defined input values | Excel Discussion (Misc queries) | |||
Editing Data Validation Input Messages | Excel Discussion (Misc queries) | |||
Can validation input messages be locked in place? | Excel Discussion (Misc queries) | |||
Extract Data Validation Input Messages and Titles | Excel Programming | |||
Remove Data validation Input messages | Excel Programming |