VBA to Restrict user either edit or delete the row values

[Malhotra Rahul]

Hi, I am looking for the VBA Script where i can restrict to user either change the values or delete from range A3:AF3 in Sheet "Raw Data". Request to you please help me.

Thank you in advance

 

[Yongle]

Test to see if this does what you want...

[COLOR=#000080]Paste code below into "Raw Data" [B]sheet[/B] module[/COLOR]
(right-click on sheet tab \ click View Code \ paste code into window on right)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A3:AF3")) Is Nothing Then
        'allow edit or delete
    Else
        'undo changes
        With Application
             .EnableEvents = False
             .Undo
             .EnableEvents = True
         End With
    End If
End Sub

 

[Malhotra Rahul]

Hi Yongle, Thank you for your support. I have tried it already but its getting debug at .Undo

 

[Yongle]

That is unexpected
- the code is not complex
- it works for me
- perhaps there is something else happening in your worksheet


Let's prove that the code works first:
Try
- creating a new workbook
- pasting the code in the sheet module for Sheet1
- amend values in rows 2 and 3

Please let me know how you get on and which version of Excel you are using

 

[Malhotra Rahul]

Hi Yongle, i am using excel 2013 and the script is not allowing make any changes except for Row 3 and i do need not to make any changes neither edit nor delete for row 3 from range A3:AF3.

 

[Yongle]

The code is working correctly
- it allows edit or delete in row 3
- it does not allow changes in other rows

restrict to user either change the values or delete from range A3:AF3

The code is doing exactly what you asked for in post#1


If that is not what you want please give me examples of what you want
What happens if user wants to edit/delete
cell A2
cell A3
cell A4
cell AF3
cell AF4

thank you

 

[Malhotra Rahul]

Thank you Yongle, I found what i was looking for. Thank you for your support.

 

[Yongle]

How about posting your solution here so that others learn from you
Thanks

 

[Malhotra Rahul]

Hi Yongle i have used the below script which i found more suitable. First you need to protect cell by clicking right cell in Formatting option then apply the below script in the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Locked Then
        MsgBox "This Cell Is Locked!", vbInformation, "Worksheet Protection"
        Range("A1").Select
    End If
End Sub

the VBA script you provided, requires a small change to match with my requirement instead of "If Not Intersect" it should be "If Intersect".

 

[Yongle]

Thanks for for posting your solution.

the VBA script you provided, requires a small change to match with my requirement instead of "If Not Intersect" it should be "If Intersect".

or remove Else