worksheet_change problems

[MrJoosten]

Hi guys,

I've written some code to protect certain ranges from unnoticed editing. unfortunately its not working yet.

Ranged H9 - X(COUNTED nr) must activate the macro.

Sub worksheet_change(ByVal Target As Range)


Dim rng As Range
Dim R As Long
Dim txt As String


'count used cells
R = Cells(Rows.Count, "A").End(xlUp).Row


'text for msgbox
txt = "Are you sure you want to change the cell?"


'declare ranges ( i think here is my mistake )
Set rng = Application.Union(Range("H9:H" & R), Range("J9:J" & R), Range("L9:L" & R), Range("N9:N" & R), Range("P9:P" & R), Range("R9:R" & R), Range("T9:T" & R), Range("V9:V" & R), Range("X9:X" & R))




'Get old value from target
Oldvalue = Target.Formula




If Not Application.Intersect(Target, rng) Is Nothing Then


'get new value from target
Currentvalue = Target.Formula
    
    
    If Oldvalue = Currentvalue Then
        Application.EnableEvents = False
        Exit Sub
        Application.EnableEvents = True
    End If
  
        Response = MsgBox(txt, vbYesNo, "Watch out!")
                If Response = vbNo Then
                    Application.EnableEvents = False
                    Application.Undo
                    Application.EnableEvents = True
                    Exit Sub
            
                Else
                    Target.Select
                    Exit Sub
                End If
End If
    


End Sub

Thanks for looking at my code.

 

[offthelip]

I can immediately see one problem, see my annotation below:

'Get old value from target
Oldvalue = Target.Formula


If Not Application.Intersect(Target, rng) Is Nothing Then




'get new value from target
Currentvalue = Target.Formula
 ' when you get to here oldvalue will alway be equal to newvalue   
    
    If Oldvalue = Currentvalue Then
 ' so you will always get to here   
    Application.EnableEvents = False
' this will disable events, which effectivley stops everything working and then
        Exit Sub
' you exit the sub so your copy of excel in now not working because all events are disabled.  brilliant!!