gathrawnca
06-28-2012, 07:37 AM
I am trying to write code in order to redirect the event when you click on insert or delete in the column command bar to my own macros. What I have tried so far is this:
Private Sub WorkSheet_Activate()
Dim DeleteControl As CommandBarControl
Dim InsertControl As CommandBarControl
'Find delete control in column context menu and redirect the click event to DeleteColumn macro
Set DeleteControl = Application.CommandBars("Column").FindControl(ID:=CommandBars("Column").Controls("Delete").ID, Recursive:=True)
DeleteControl.OnAction = ThisWorkbook.Name & "!DeleteColumn"
'Find insert control in column context menu and redirect the click event to InsertColumn macro
Set InsertControl = Application.CommandBars("Column").FindControl(ID:=CommandBars("Column").Controls("Insert").ID, Recursive:=True)
InsertControl.OnAction = ThisWorkbook.Name & "!InsertColumn"
End Sub
This code sometimes works fine but usually it gives me a runtime error 5: invalid procedure call or argument on the Set statement.
I have also tried:
Private Sub Workbook_Open()
Dim DeleteControl As CommandBarControl
Dim InsertControl As CommandBarControl
'Find delete control in column context menu and redirect the click event to DeleteColumn macro
Set DeleteControl = Application.CommandBars("Column").FindControl(ID:=294, Recursive:=True)
DeleteControl.OnAction = ThisWorkbook.Name & "!DeleteColumn"
'Find insert control in column context menu and redirect the click event to InsertColumn macro
Set InsertControl = Application.CommandBars("Column").FindControl(ID:=3181, Recursive:=True)
InsertControl.OnAction = ThisWorkbook.Name & "!InsertColumn"
End Sub
This usually works although I have found that sometimes the control IDs for the same control are different. For example, sometimes the ID for the Insert command bar control was 3183 not 3181.
I am developing this on Excel 2003 right now but it is more important that it runs on Excel 2010. I am using Windows 7.
Any ideas or tips would be appreciated.
Private Sub WorkSheet_Activate()
Dim DeleteControl As CommandBarControl
Dim InsertControl As CommandBarControl
'Find delete control in column context menu and redirect the click event to DeleteColumn macro
Set DeleteControl = Application.CommandBars("Column").FindControl(ID:=CommandBars("Column").Controls("Delete").ID, Recursive:=True)
DeleteControl.OnAction = ThisWorkbook.Name & "!DeleteColumn"
'Find insert control in column context menu and redirect the click event to InsertColumn macro
Set InsertControl = Application.CommandBars("Column").FindControl(ID:=CommandBars("Column").Controls("Insert").ID, Recursive:=True)
InsertControl.OnAction = ThisWorkbook.Name & "!InsertColumn"
End Sub
This code sometimes works fine but usually it gives me a runtime error 5: invalid procedure call or argument on the Set statement.
I have also tried:
Private Sub Workbook_Open()
Dim DeleteControl As CommandBarControl
Dim InsertControl As CommandBarControl
'Find delete control in column context menu and redirect the click event to DeleteColumn macro
Set DeleteControl = Application.CommandBars("Column").FindControl(ID:=294, Recursive:=True)
DeleteControl.OnAction = ThisWorkbook.Name & "!DeleteColumn"
'Find insert control in column context menu and redirect the click event to InsertColumn macro
Set InsertControl = Application.CommandBars("Column").FindControl(ID:=3181, Recursive:=True)
InsertControl.OnAction = ThisWorkbook.Name & "!InsertColumn"
End Sub
This usually works although I have found that sometimes the control IDs for the same control are different. For example, sometimes the ID for the Insert command bar control was 3183 not 3181.
I am developing this on Excel 2003 right now but it is more important that it runs on Excel 2010. I am using Windows 7.
Any ideas or tips would be appreciated.