CODEWITHSUNDEEP
excelvba
ides
ides

Reputation: 369

Excel macro code for clearing formulas in cells does not work when the sheet is protected

After some googling I finally found some code where I could prevent users from placing formulas inside cells. It works great, that's until I protected the sheet. Can anyone tell me what I'm doing wrong? I'm really new to VB. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
Range("I39").SpecialCells(xlCellTypeFormulas).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End If
End Sub

The entire code for my sub is as follows. I need to stop users from pasting in the cells and putting formulas in them.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C26")) Is Nothing Then
        Application.CutCopyMode = True
        Application.EnableEvents = False
        On Error Resume Next
        Range("C26").SpecialCells(xlCellTypeFormulas).ClearContents
        On Error GoTo 0
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 0

Views: 704

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

Here is a version that facilitates formula checking over a range of cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rNoFormulas As Range
    Set rNoFormulas = Range("C26:I26")
    If Intersect(Target, rNoFormulas) Is Nothing Then Exit Sub
    If Target.HasFormula Then
        Application.EnableEvents = False
        Target.ClearContents
        MsgBox "formulas not allowed in cell " & Target.Address
        Target.Select
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 1

mat duwe
mat duwe

Reputation: 153

If you just want to protect certain cells only, no vba code is need. follow this step : Open sheet that contains cells or columns that you want to protect, press ctrl while selecting those cells or column to be protect, then right click, choose format cells, choose protection tab and uncheck the locked option. those cells or column will not be locked although you have protected the sheet. default setting is all cells in the sheets is locked so you must choose which cells you want to unlock while protecting the sheet. you may record a macro if you still want to use vba. hope this help

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

If you want to allow data entry in cell C26, but not formula entry, then use the Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rNoFormulas As Range
    Set rNoFormulas = Range("C26")
    If Intersect(Target, rNoFormulas) Is Nothing Then Exit Sub
    If rNoFormulas.HasFormula Then
        Application.EnableEvents = False
        rNoFormulas.ClearContents
        MsgBox "formulas not allowed in cell C26"
        rNoFormulas.Select
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 1

Related Questions