Thursday, April 2, 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

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog