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

 

[shinigamilight]

Why do you need a vba code? Why not just manually lock those cells and protect the sheet?

 

[FGaxha]

Hi ,
I am looking for: Prevent updated on cell range if SHEET NAME IS "MASTER"
thanks

 

[Maklil]

@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

 

[Fluff]

@Maklil You need to change the placement of this line Application.EnableEvents = False otherwise you are going to permanently disable events.

 

[Fluff]

Prevent updated on cell range if SHEET NAME IS "MASTER"

As has already been suggested, why not just protect that sheet, so that people cannot change the cells?

 

[Maklil]

@FGaxha
move first line after third line to become

If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C33:F52")) Is Nothing Then
Application.EnableEvents = False

 

[FGaxha]

Guys, Thank you so much, it works perfectly.
I was using :
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) to get Tab Name to Cell A1 and then
Custom Data Validation =$A$1 <>"Master".

VBA: work great
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

 

[Fluff]

Thank you so much, it works perfectly.

I would recommend you change the macro as per post#7, other wise you could be in trouble.

 

[FGaxha]

I did,
This work great:
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