Hello,

Could you have any idea how to do it ?. I was looked anywhere and not
founded any information about that.
I know, i may use the workbook onchange or calculate event, but i must
necessarily get it just at this event moment.
Please, help me !!!

Thx in advance
Grzegorz

I believe you are out of luck, Grzegorz. No such event. You must play around
with combinations of selectionchange, change and calculate events to trap
what you look for.

Or just protect the worksheet. No columns can be added or deleted from then
on without your very own column_insert macro.

HTH. Best wishes Harald


"Grzesiek" skrev i melding
...
Hello,

Could you have any idea how to do it ?. I was looked anywhere and not
founded any information about that.
I know, i may use the workbook onchange or calculate event, but i must
necessarily get it just at this event moment.
Please, help me !!!

Thx in advance
Grzegorz

Hi Grzesiek,

I just asked a similar question on this forum, and got a good answer. Try
the following code:


Public Sub MyInsertRow()
Dim Mycontrol As CommandBarControl
Set Mycontrol = Application.CommandBars("Worksheet Menu Bar").FindControl
_(ID:=296, Recursive:=True)
Mycontrol.OnAction = ThisWorkbook.Name & "!mymacro"
End Sub

Public Sub MyMacro()
MsgBox "Hi"
End Sub

When "Insert Row" is then selected, MyMacro will run instead. If you really
want to insert a row, your macro will need code to make this happen. To
reset the menu item to get the standard menu "Insert Row" to work, use the
code:

Public Sub MyResetInsertRow()
Dim Mycontrol As CommandBarControl
Set Mycontrol = Application.CommandBars("Worksheet Menu Bar").FindControl
_(ID:=296, Recursive:=True)
Mycontrol.OnAction = ""
End Sub

For "Insert Column" use ID:=297. Good luck!

Hi Filibuster

It's cool !!! , thank you it's work, but i found one way in this
solution , where my macro isn't work - i don't know why (i think, i
must use other id, but which ? ) , I was tested with :
Set Mycontrol = Application.CommandBars("Worksheet Menu
Bar").FindControl(ID:=296, Recursive:=True)
Set Mycontrol2 =
Application.CommandBars("Cell").FindControl(ID:=31 81, Recursive:=True)
Set Mycontrol3 =
Application.CommandBars("Cell").FindControl(ID:=29 2, Recursive:=True)
and in my opinion Mycontrol2 must worked when I click right mouse
button (insert) on the number row in Excel, but this solution not work
for this - its work when i execute this on any cells in excel.
Do you know which id i must use for this ?

Thank you again

Grzes,

Your ID values look correct for the Insert and Delete functions of the right
click menu. I tried your code, and it works for me. Did you remember to
assign a macro to your newly defined controls:
MyControl2.OnAction = ThisWorkbook.Name & "!mymacro2"
MyControl3.OnAction = ThisWorkbook.Name & "!mymacro3"

Hello,

It's working ok. now, thank you very much again.


Best regards

Grzegorz