VBA to Restrict user either edit or delete the row values

Malhotra Rahul

Board Regular
Joined
Nov 10, 2017
Messages
92
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
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Test to see if this does what you want...

Code:
[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
 
Last edited:
Upvote 0
That is unexpected :confused:
- 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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
How about posting your solution here so that others learn from you :)
Thanks
 
Upvote 0
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.

Code:
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".
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top