Can't create a commandbar on workbook_open

[hogstrom]

Hi all of you willing to help.

I have created a addin which on open should create some different commandbars. The worksheet menu bar gets added without any problem but the "Cell" ones do not. I don't get any errors and if I try to run the code after the workbook has been opened, the code works fine. I have added the code below and appreciate any answers.

The workbook_open sub looks like this

Private Sub Workbook_Open()
InstallAddIn 'Checks if there is a new addin avialable and installs this. Works just fine!
AddMenus 'Adds a Worksheet Menu Bar. Works just fine
RClick 'Do not work
RChartClick 'Do not work
End Sub

The RClick sub looks like this

Sub RClick()
Application.CommandBars("Cell").Reset
With Application.CommandBars("Cell").Controls
With .Add(Type:=msoControlButton)
.Caption = "Calculate Sheet"
.FaceId = 263
.OnAction = "CalculateSheet"
End With

With .Add(Type:=msoControlButton)
.Caption = "Calculate Range"
.FaceId = 263
.OnAction = "CalculateRange"
End With

With .Add(Type:=msoControlButton)
.Caption = "Calculate Row"
.FaceId = 263
.OnAction = "CalculateRow"
End With

With .Add(Type:=msoControlButton)
.Caption = "Insert range to PPT"
.FaceId = 267
.OnAction = "insert_range"
End With
End With
End Sub

Thank you in advance!!!!

Erik

 

[combo]

For me the code works, cell's right-click popup menu is updated. Have you tried to debug the code (you can insert [tt][blue]Stop[/blue][/tt] in the code and execute it line by line? Is there a code that can reset the menu again?

combo

 

[hogstrom]

Hi combo,

I think you are right, there is another addin that is visible in the VBA Editor but not in the Tools-->Addin list.
This addin adds buttons to the commandbar add probably uses the reset preoperty.

Is it possible to make my code run after this code? The last resort would be to try to run a password recovery on the other addin an disable the reset property. The addin is installed through a execution file and is only visible in the editor, how do I recover a password on that file, is this possible and is there any good freeware available?

Thanks!

 

[combo]

It's likely that it is not an add-in but a regular workbook with hidden window. There are some possibilities here:
- personal macro workbook (but it wouldn't be protected),
- a workbook located in startup path.
In both cases you can unhide window with window>unhide. To find the startup path, search for XLSTART path, in the tools>options: general tab, check the alternative startup path. If you are able to unhide window, try 'save as', excel will point to the directory with active file.

Check security settings, you should be at least warned that excel opens a file with macros. I would be cautious with unknown add-ins, otherwise I would ask the author to remove a part of disturbing code.

Debug your original code with 'Stop' inside, continue execution line by line. You will see if your code creates the menu (you can switch to excel and right-click any cell).

combo

 

[hogstrom]

There is no hidden windows available.

When I try the Stop inside the code, the commandbars gets added correctly but is then removed by the other macro.

The workbook or addin, or whatever it is, is not visible in the VBA editor list of VBAProjects, it is somehow opened after all other macros have ben executed and is visible in the list after that my code has been executed.

The code that is running is corporate template macros, this is why I don't want to/can't edit that code.

Can I somehow activate a macro when I activate a cell at the first time (or every time if needed) and throug this make sure that my code is executed after the corporate macro?

Thanx so much for your help combo!

 

[combo]

Can you consider to add your custom commandbar instead of changing popup menu that is modified by another add-in? If you execute your macro after corporate add-in, you will reset changes made by it.

To find the corporate add-in, uninstall your own one. In VBE, display the object browser and check libraries (top drop-down), if you select any library, you can see its path and name (so the type too). Depending on the extension ('xls', 'xla', dll, exe etc.) possible action can be differnt.
COM add-in: add 'COM add-ins' to the menu ('Tools' category) and execute it (check for COM add-ins).



combo