Command Bar Control Error

[gathrawnca]

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.

[CatDaddy]

http://stackoverflow.com/questions/593117/how-to-programmatically-add-a-toolbar-button-and-onclick-handler-to-excel

[gathrawnca]

That link looks fine if I'm trying to create my own Command Bar Control but I'm not. Just trying to redirect the standard column command bar controls to my own macros.

[CatDaddy]

ah my mistake

[Bob Phillips]

Shouldn't it just be

Set DeleteControl = Application.CommandBars.FindControl(ID:=294, Recursive:=True)

[gathrawnca]

See that's what I had at first but the control ID's are not always the same it seems. As I mentioned, at least the Insert Command control has had ID 3181 and 3183. No idea why it wouldn't be the same all the time.

[Bob Phillips]

It's a long time since I have played with Commandbars, it's all ribbon now, but is this because the control could be on multiple commandbars?

[gathrawnca]

I don't know. It's not all ribbon now. There are still the column command bars in 2010 (maybe a more common name for them would be column context menu but they're refered to as command bars in the vba code).

[Bob Phillips]

Yeah, I know, what I meant is that I do plenty of ribbon customising but I haven't done any commandbar customising for a few years now.

[gathrawnca]

Quick note for anyone trying this:
If you end up getting it to work,

Application.CommandBars("Column").Reset

resets the controls to default.

[otoni]

Hello, I'm having similar error on current days still :P
how did you manage to solve the problem with IDs 3181 and 3183, failing sometimes?

[Paul_Hossler]

Welcome to VBAexpress

Please take a minute and read the FAQ in the link in my signature

This is an 11 year old post so it's always better in cases like that to start your own, include all information and error messages, and some code or a workbook that shows the error