Prevent update/entries on cell range based on sheet/tab name?

[FGaxha]

Hi,
VBA Code: How to prevent update/entries on cell range "C33 :F52" if Tab Name is "MASTER" else ok

 

[Fluff]

You need to get rid of the line in red

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C33:F52")) Is Nothing Then
Application.EnableEvents = False
        cng = Target.Value
        Application.Undo
        Target.Value = Target.Value
        Else
    End If
    Application.EnableEvents = True
End Sub

 

[FGaxha]

Actually there is an issue:
It work for firs Tab Master, then it take action to any tab no matter the Tab Name.
So I would like to prevent only Tab Named "Master" range C33:F52.

 

[FGaxha]

@FGaxha
try this
copy and paste in MASTER sheet module
if the sheet name is MASTER will not update ,if change sheet name will update

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C33:F52")) Is Nothing Then
    If ActiveSheet.Name = "MASTER" Then
        cng = Target.Value
        Application.Undo
        Target.Value = Target.Value
        Else
    End If
    End If
Application.EnableEvents = True
End Sub

Sir,
It does not work

 

[FGaxha]

Sir,
It does not work

YES, it work Great.
My fault: Tab/Sheet name was "Master" not "MASTER" with all upper case it works great.
Thanks again

 

[Maklil]

@FGaxha
may you select answering in post#11? the whole code has organized , until others members know where is the exactly solution .
thanks

 

[Fluff]

@FGaxha
The code will not stop people from deleting multiple cells, rows or columns from within the "C33 :F52" range. Nor will it stop people from inserting cells into that range.

 

[FGaxha]

@FGaxha
The code will not stop people from deleting multiple cells, rows or columns from within the "C33 :F52" range. Nor will it stop people from inserting cells into that range.

Hi Fluff,
Thanks for being active to my post.
To archive your query I use this protection as below: the sheet is unprotected but no one can insert, delete columns and rows.
VBA code is needed for different purposes. Is needed to keep cell range "C33:F52" blank.

 

[Fluff]

If the sheet is not protected then none of those settings will make any difference.
Also as I said before the code supplied will not stop people from changing the data in that range.

 

[FGaxha]

If the sheet is not protected then none of those settings will make any difference.
Also as I said before the code supplied will not stop people from changing the data in that range.

Well,
My goal was:
To keep Tab/Sheet "MASTER" as e template sample, so no one can make entries on range "C33:F52" if sheet name is MASTER.
I have some other macro code to prevent changing sheet name from "MASTER", so if this Master template is with Different name, Master 001 then range :C33:F52" is free for data entries.
So it works for me.
Ofcourse sheet is protected but is preventing only to insert or delete rows and columns. There is other protection to prevent entries data in tab/sheet but I do not need it right now.
You can try:
On tab/sheet right click, Protect Sheet, then select all as below except "Insert Column ,Insert Rows, Delete Column, Delete Rows.
The worksheet is protected only for: Insert Column ,Insert Rows, Delete Column, Delete Rows.
Select all work sheet Ctrl+!, unselect Lock enter password.

 

[Fluff]

You stated that

the sheet is unprotected

In which case none of those selection will make a difference, but you now say

enter password.

In which case the sheet is protected.

After unlocking all cells, select range C33:F52 & make them locked, then no one can enter data into them.