excel triggers 1

[faxof]

is there a any code to detect whether a user has deleted a column or row and then undoing that procesure?

faxof

 

[DaleWatson123321]

faxof,

I see no one has yet "jumped in" on this, so perhaps it suggests there is no "easy" solution.

I've developed a working model, using the following Worksheet_Change event for Sheet1...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
numcolms = Range("t_c&quot.Columns.Count
numrows = Range("t_r&quot.Rows.Count
If numcolms < 255 Then
Application.Undo
End If
If numrows < 65535 Then
Application.Undo
End If
End Sub

Note: The above routine references range names &quot;t_c&quot; and &quot;t_r&quot;. Therefore you'll need to create these range names (or use your own choice of names).

1) I've assigned &quot;t_c&quot; to A65536:IU65536, and I've hidden Row 65536.

2) I've assigned &quot;t_r&quot; to IV1:IV65535, and I've hidden Column IV.

Note: It's important... NOT to assign &quot;t_c&quot; to ALL the Columns (don't include IV), and NOT to assign &quot;t_r&quot; to ALL the rows (don't include 65536). This is because if you were to include ALL the rows/columns, then the range names would remain &quot;anchored&quot; and would therefore NOT change whenever a row or column is deleted.

I hope this helps. If you'd like, I can email you a copy of the model I created.

Please advise as to how this fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[faxof]

cheers for that dale - i'll have a go in the morning when i get to work, i wonder if it'll make life easier if you do send me the working version you made; although, i'll still have a go at creating it without &quot;cheating&quot;

many thanx
faxof

faxof@aol.com

 

[faxof]

dale

a couple of probs (and maybe a solution)
i wanted the users to insert columns (with my macro buttons)

i couldn't use my add col macros with your code, i did try but got stuck.

so i changed it a bit; i put an &quot;x&quot; in cells(256,65536)
this stops people adding cols and rows.

then - on worksheet change - i checked to see if the &quot;x&quot; was still there (that checks to see if the user deleted a col or row) then do application.undo

works fine - only problem is that it crashes on some columns and not on others and i can't work out why - i just can't work out why i get an error sometimes and not others. . .