Thursday, 2 April 2015

How to Disable Editing Cells in Excel with Data Validation using VBA Macro

VBA Macro to Disable Editing Cells in Excel with Data Validation
To Disable Editing Cells in a Selected Range:
Sub Disable_Editing_Cells()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=" "
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "You Cannot Edit the Cell"
        .InputMessage = ""
        .ErrorMessage = "The Cells You are Trying to Edit has a Data Validation , Still if you want to edit Please clear the Data Validation Rules"
        .ShowInput = True
        .ShowError = True
    End With
End Sub
-----------------------------------------------------------------------------------------------------------------
To Enable Editing Cells in a Selected Range:
Sub Enable_Editing()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Alert Styles :
AlertStyle:=xlValidAlertStop
AlertStyle:=xlValidAlertWarning  
AlertStyle:=xlValidAlertInformation

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts