capture column delete

[starl]

Is there a way of capturing a delete column? According to the help, a Worksheet_Change will ignore it.. is there anything else I can try?

thanks

 

[Legacy 98055]

Hi Starl.
It seems that none of the events are fired. I tried using the Calculate event along with some formulas to see if this event would be fired with a delete. What are you trying to do overeall? Maybe someone can come up with an alternative???
Tom

PS A long winded approach? Replace the "Delete" commandbarbutton on the "Cell" popup with your own code. You could then easily capture which column, row, cell, ect... was being deleted.
This message was edited by TsTom on 2002-08-25 13:02

 

[starl]

Hi Tom - thanks for looking into this.
I have a worksheet with some some columns that I can't allow to be deleted. But the user wants to have the ability to add/delete rows. And I don't want to protect the sheet. That's why I was hoping to capture an action...
btw, I didn't understand your PS - would be you explain it to me
thanks!

 

[Legacy 98055]

Hi Starl.
One other alternative would be to disable the delete commandbarbuttons whenever your users are in specific columns or rows. One drawback... The user will be unable to select "Entire" rows or columns. Any other selection is allowed. Perhaps someone can get around this? Anyway, which rows and/or columns are we speaking of?

The right-click popups "Row", "Column", "Cell" will have the "Delete" option greyed out. Will need to compensate for the Worksheet Menu Bar, File, Delete... as well. Are there any keyboard shortcuts for deleting a row or column?
Right click on your worksheet tab, paste the following code in... Try and delete row 2 or column 1. Remember, I could not figure out how to get this to work while allowing the user to select entire columns or entire rows???

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SeeControl As Boolean
If Target.Column = 1 Or Target.Row = 2 Then
    SeeControl = False
Else: SeeControl = True
End If

If Selection.Columns.Count = 256 Then ActiveCell.Select
If Selection.Rows.Count = 65536 Then ActiveCell.Select

Application.CommandBars("Column").Controls("&Delete...").Enabled = SeeControl
Application.CommandBars("Row").Controls("&Delete...").Enabled = SeeControl
Application.CommandBars("Cell").Controls("&Delete...").Enabled = SeeControl
Application.CommandBars("Ply").Controls("&Delete").Enabled = SeeControl

End Sub

Tom

 

[starl]

Tom - that suggestion sounds great! I didn't even think of disabling the ability! I don't have time to play with it now, but THANK YOU for the idea!

 

[starl]

ok. I posted the code. Two things:
I don't care if they want to delete rows so I removed the lines pertaining to rows - and now I can't select rows on that sheet. Why does it matter?
It compiles fine, but at runtime, it can't find the commandbars - it doesn't recognize 'em. How can I find out what it wants?

 

[starl]

hmm.. if I select a cell in that column, the delete is blanked out... so it's somewhat working. Can you explain this to me?
also, what is the commandbar "ply"

 

[starl]

making this Q visible....

 

[starl]

ok, I got some stuff figured out....
I have to different right-click menus depending on cell or column.
The cell control is "&Delete..."
the column control is "&Delete"

what can I do to the code to get it to work correctly? I want it to SKIP a cell selection. I only care if an entire COLUMN is selected. Other than having it skip errors....

HELP! PLEASE!!!!

 

[smozgur]

Hi starl,

I am not sure you would like this but this code will prevent user selecting columns or rows.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Columns.Count = 256 Or Target.Rows.Count = 65536 Then Application.Goto Range(Cells(Target.Row, Target.Column), Cells(Target.Row, Target.Column))
End Sub

May be?

Suat