Explanation of syntax for Workbook_SheetChange?

[Lil2606]

Hi all,

I'm trying to set up "protection" on my sheets, and so far sheet.protect doesn't do the job, because I have tables and I don't want my users to be able to add columns to the tables but AllowInsertingColumns = False still allows table columns to be added. So I think I have a solution, only I am not that good in VBA yet to be able to implement it... or to be exact I would rather not copy a code then change a few bits and be surprised if it does something unexpected because I didn't understand it to begin with..

So on Sheet4, I have a table in A10:M30 not including the headers, this is the range that doesn't need protection, my users need to be able to add and delete rows and change data in the cells, but at the same time this is the range they are not allowed to add columns to.

My idea is to use Workbook_SheetChange, because I have 4 sheets, and based on which sheet it is a different range needs protection, and this way it would be all in one place.. but if thats a bad idea please do tell and explain!

So on Sheet4, A9:M9 is my header row range, and I would make that the Target, and do a quick loop, count the cells in this range, and if its not 13 then (the user tried to add or delete a column) which is not allowed so: application.undo msgbox "You are not allowed to do that"

but I don't understand the syntax of this Workbook_SheetChange event... Sh is the sheet it is referring to.. the Activesheet, or can I specify
Sh = Sheet4? Or does that need to be part of an if block? Like: If Sh=Sheet4 then Target = Sheet4.Range("A9:M9") then do the loop if the count is not 13 then undo end if end if? Or how does this work??
Target is the Range I'm asking it to keep an eye on, so if that range changes then this code runs.. but how do I Dimension that Target (range)?

I can see this:

If Not Intersect(Target, Target.Worksheet.Range("your range here")) Is Nothing Then

on many websites but I am so confused by this line... why intersect? Intersecting the Target (you specify?) with the Target(you specify??), and if it IS nothing then the code wouldn't trigger at all, so why is "If Not .... Is Nothing" necessary here?

Can someone explain how does this work please?

Thank you!

 

[Lil2606]

Okay, so Worksheet_Change works like a charm now, if the Time Stamp on Sheet1 exists, then it "undoes" any changes I try to do.. but out of curiousity I tried to bypass it with a macro, entering a value into one of the "Protected" cells with a macro, and it "makes it possible for itself", by removing the Time Stamp from Sheet1.. Any ideas why?

Sheet4 Code:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim LastRowSec As Long


LastRowSec = Sheet1.Range("A:A").Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


If Sheet1.Range("D" & LastRowSec).Value = "" Then


GoTo Finish


End If


If Not Intersect(Target, Sheet4.Range("A:A")) Is Nothing Then


Application.Undo


End If


Finish:


End Sub

and in a module I have

Sub Try
Sheet4.Range("A10").Value="Something"
End Sub

 

[RoryA]

First, you should disable events before undoing the changes, otherwise you will trigger the change event again.

Second, I don't know what this means:

it "makes it possible for itself"

 

[Lil2606]

Yeh I had Application.EnableEvents = False before and I just put it back cause it died without it. (And now reading your comment I suddenly understood why its necessary)

"Makes it possible for itself" means: I have a value (a Time) in

[COLOR=#333333]Sheet1.Range("D" & LastRowSec)[/COLOR]

so if any changes happen in

[COLOR=#333333]Intersect(Target, Sheet4.Range("A:A"))[/COLOR]

it should

Application.Undo

but when I run the "Sub Try" instead of throwing an error, or inputting "Something" in A10, then undoing it, it deletes the value in

[COLOR=#333333]Sheet1.Range("D" & LastRowSec)[/COLOR]

, so the first If bolck

[COLOR=#333333]If Sheet1.Range("D" & LastRowSec).Value = "" Then[/COLOR]

becomes true and the "Sub Try" just changes A10's value to "Something"

 

[RoryA]

OK, I understand now.

The undo will unfortunately undo whatever the last action in the undo stack was. That will not however include anything that was done by code, which is why you see what you are seeing.

 

[Lil2606]

Oh now I see what it is doing! Thanks!