buttons on command bars

[Graham Taylor]

I recorded the following code in Excel
Application.CommandBars("RebarToolbar").Controls.Add Type:=msoControlButton, _
ID:=2089, Before:=6
as i want to add buttons to a custom toolbar.
but it doesn't work, why?

[Nitro]

  Application.CommandBars.Add(Name:="Custom 1", Position:=msoBarTop, Temporary:=True).Visible = True
  Application.CommandBars("Custom 1").Controls.Add Type:=msoControlButton, Id:=2950, Before:=1
  Application.CommandBars("Custom 1").Controls.Item(1).OnAction = "Macro1"
  
  'Delete
  'Application.CommandBars("Custom 1").Delete

[chander]

hey nitro i tried this code .. i m also facing same problem but in my case it says , Add method of Commandbars object failed....

when i debug the code it shows msoControlButton value as Empty ... i see the Excel making visible true , my command bar get added but error comes while adding control to it ..

[JordanChris]

Does this help?:

A new command button can be added into any of the major MS Office applications (not Outlook - that has to be done differently), by running a short piece of VBA code.

Sub Add_RegFile_Button()
' First delete the added buttons
Set Buttons = CommandBars("Standard").Controls
For Each Control In Buttons
    If (Control.BuiltIn = False) _
        And Control.Caption = "MY Reg Filer" Then 
        Control.Delete
    End If
Next Control
 
Set newItem = _ 
     CommandBars("Standard").Controls.Add(Type:=msoControlButton)
With newItem
    .BeginGroup = True
    .Caption = "MY Reg Filer"
    .FaceId = 455
    .OnAction = "MYRegFile"
End With
End Sub

This macro is in two parts – the first part deletes any button with the same name that you are about to add. This “delete first” allows you to run the macro as often as you like, without new buttons being added each time.

The second part adds the new button in to the “Standard” command bar.
· The .OnAction calls another sub routine VBA macro.
· The .Caption is what the user sees when they hover the mouse over the button.
· The .FaceID is the number of an icon to appear on the button. Getting your own icon on the button might be possible, but it does not seem to be documented as to how to do it!
See the MS Office 2000 Visual Basic Programmers Guide for more details.

[chander]

ya it works .. actually instead of macro i was writting this code in standard VB Exe project and i want that once that exe run , my new toolbar button and bar should come in excel application automatically when ever excel starts .. actually i didnt added MS Office Object library so i was facing above problem now that have been solved . As all these constants to Command bar and button etc are given in Office library not in Excel library so tht problem was coming as it failing to add buttons ...

but still i got a problem with that .. when i run my exe it adds the command bar and control but after closing of exe these command bar and control goes away .. i think they remain only in current instance of Excel Application which my VB proj creates.I want to associate these command bar and buttons globally to Excel after once my exe runs. After that when ever excel gets open my command bar and button should be there. That is difference case if some one removes them manually in Excel. here is my code which i m executing.... it add command bar and button only in current instance of Excel not globally.. once exe is unloaded , command bar and buttons also goes off ...

here is code ....

Dim excelObj As Excel.Application

Set excelObj = Application

Application.CommandBars.Add(Name:="Session", Position:=msoBarTop, Temporary:=True).Visible = True

Application.CommandBars("Session").Controls.Add Type:=msoControlButton, Temporary:=False

Application.CommandBars("Session").Visible = True

Application.CommandBars("Session").Controls(1).Caption = "Session 1"

Application.CommandBars("Session").Controls(1).Style = msoButtonCaption
Application.Visible = True

it adds and show excel application .. but as excel object goes off from memory my command bar and button also goes off ...

by what mean i can associate these with Excel.. i dont want to use templates for this ...

[JordanChris]

VB controls the instance of an Excel application. So anything you set will be for that instance only.
Within an .XLS will control that spreadsheet, for anyone hwo opens that spreadsheet.
Within a template will control the Excel application itself for any time Excel is started with that template.

So (as far as I know) you need to use templates.
I would have thought that there should be a way of controling these fromn VB, but not sure how.

[chander]

yes u r right but what if some one replace or remove default template then my macro code wont run i dont want some one use my application to run my macro or template code before excel opens. i want excel starts and my application link should be there by which i can call my further macro or VB code. if some one removes those default template my macro code wont run .. so wat i want i just want to put a button or link in excel for ever ( till user doesnt remove it mannualy) so that when clicking on that my app will work further then i will attach my template etc.

there should be some thing apart from template which would have triggered when excel starts ...

[chander]

hey cris i got solution for that .. it was quite simple but i think we all messed up that and didnt notice it .. well while adding command bar there in required parameters there is one param Temporary ... that is whether u want ur command bar to put in excel temporary or permanently .. so make Temporary to False it would in Excel till some one doesnt remove it manually ..

i m using OnAction property for my next code .. how i can specify a macro to run which is in some template or excel sheet .. i m trying it like this ...

excel.Application.CommandBars("Session").Controls(1).OnAction = "C:\test\Book.xlt | testing"

where testing is maro name in given template .. but it says can not find given macro how ever if i run it by Application.run method it executes .. any tip , how to use OnAction property ??

[JordanChris]

Is the macro called "testing"?
Or is there a subroutine called:
Sub testing

Or both?
I have used the subroutine called "Sub testing" approach, and that worked OK for me - but the subroutine was in the original spreadsheet, not in a separate template.

[chander]

testing is simple macro name or can be used as subroutine too ..
its in a template called book.xlt .. when i run my previous code it shows me just Excel without any work book so my that command button will open that template and run macro .. which associate itself with excel .. but when i try same code with excel.application.run method it runs that macro but not going with OnAction .. i checked out at msdn.microsoft.com that it accepts VB procedure and method also but they havnt given any suitable example for it .. in another post i checked out some one has done it from VB code .. i m waiting for his reply as he is in US and i will get his reply today night ..

[chander]

hey i got solution for tht .. i got a problem with syntax .. which i just did by hit n trial .. its some thing like this ..

excel.Application.CommandBars("Session").Controls(1).OnAction = "'C:\test\book.xlt'!module1.testing"

so u have to give ! with proper sub routine name .. of macro ..