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!

 

[nemmi69]

What it means is that the target address (say $C$3) is intersects inside your provided range then the IF is true. IE if the range was $B$2:$D$4. Just imagine That the column "C" was highlighted and the row "3". They would intersect at "$C$3".

Does this help?

 

[RoryA]

That event provides you two parameters. Target is the range that has been changed and Sh is the sheet on which that range lies (so you don't need Target.worksheet - just use Sh).

The intersect tests if the changed range overlaps the specified range in any way. If it does not, Intersect returns Nothing, so testing for Not Nothing means that the changed range did overlap the range you are interested in.

 

[Lil2606]

Okay. I now understand the intersect bit.. and I'm guessing its a bad idea to do Workbook_SheetChange, if I want my targets to be different on each sheet.. Okay.. I think I got it now! Thank you all!

 

[nemmi69]

Remember that each workbook can have its own Workbook_SheetChange same as each worksheet can have its own Worksheet_SelectionChange

 

[Lil2606]

Yeah I have one Workbook with several sheets, and on each sheet I have a different range to intersect with the target, so Worksheet_SelectionChange will do the job better for me I think.. I am just familiarizing myself with this concept yet but once I understand the principal of it.. things become more obvious Ihopefully :D

 

[RoryA]

Just note that Worksheet_Change, not Worksheet_SelectionChange, is the equivalent of Workbook_SheetChange.

 

[nemmi69]

Just note that Worksheet_Change, not Worksheet_SelectionChange, is the equivalent of Workbook_SheetChange.

I thought a selection change ment that any cell on that sheet selected and that a sheet change required another sheet to be selected.

 

[RoryA]

No, Workbook_SheetChange is the Workbook-level equivalent of Worksheet_Change. There is also Workbook_SheetSelectionChange which would be the equivalent of Worksheet_SelectionChange

 

[Lil2606]

Ah I was just about to post that Worksheet_SelectionChange is acting weird.. but it was because I needed Worksheet_Change and that immediately fixed my problem.

I have a Time Stamp on Sheet1, and until that time stamp doesn't exist I don't mind what happens to Sheet4, but when it does, the data can't be changed on it anymore, so I have a Worksheet_Change with an if block to Application.Undo based on the TimeStamp, but it got really confused when I used Worksheet_SelectionChange, cause it was trying to Undo the Time Stamp on Sheet1 regardless of the fact that the Worksheet_SelectionChange was in Sheet4's code.. so it appears to me that Worksheet_SelectionChange is not limited to the Sheet that its code is written in...