Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assign OnAction to button in Add-In?

    Gary,

    The code for your "StartChase" macro should be in a 'normal' module, not in a module 'behind' a sheet.

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Assign OnAction to button in Add-In?

    Hans,

    Thanks for this - you can see how much programming I've done in Excel!<g> - I will give this a try when I get back to work tomorrow.

    Regards,
    Gary

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Assign OnAction to button in Add-In?

    Hi,

    I've been having a good wrestle creating my first Excel add-in, and am stuck on what I hope is one last snag:

    I'm using the following code to create a toolbar and button when Excel opens (the add-in will be in the startup folder) - this code is in the ThisWorkbook module of the add-in):

    <pre>Private Sub Workbook_Open()
    Dim cbrAMG As CommandBar
    Dim ctlNewBtn As CommandBarButton
    Set cbrAMG = Application.CommandBars.Add(Name:="AMG_Chaser", _
    Position:=msoBarTop, Temporary:=True)
    With cbrAMG
    .Visible = True
    Set ctlNewBtn = .Controls.Add(Type:=msoControlButton, Temporary:=True)
    With ctlNewBtn
    .FaceId = 2151
    .OnAction = "StartChase"
    .Caption = "Send Chaser Msgs"
    End With
    End With
    End Sub</pre>

    This code succeeds in creating the toolbar, the button and the tooltip. But when I click on the button to run the macro, I get an error message "The macro 'AMG_ChaserAddIn_v1.xla!StartChase' cannot be found."

    (The "StartChaser" macro is actually sitting, expectantly, in the code module behind Sheet1 of the add-in, waiting to be called.)
    Any clues on how to programatically set the correct reference to this macro for the button's OnAction property would be much appreciated - I'm guessing the difficulty has something to do with the way macros are available in add-ins but am drawing a blank at that point.

    Thanks,
    Gary

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assign OnAction to button in Add-In?

    Gary,

    Just forgot to mention this: I strongly advice you to add a few lines of code to the Workbook_Open event, and eventually even to the Workbook_BeforeClose event, to delete the toolbar you created.

    Private Sub Workbook_Open()
    Dim cbrAMG As CommandBar
    Dim ctlNewBtn As CommandBarButton
    On Error Resume Next
    Application.CommandBars("AMG_Chaser").Delete
    Set cbrAMG = Application.CommandBars.Add(Name:="AMG_Chaser", Position:=msoBarTop, Temporary:=True)
    With cbrAMG
    .Visible = True
    Set ctlNewBtn = .Controls.Add(Type:=msoControlButton, Temporary:=True)
    With ctlNewBtn
    .FaceId = 2151
    .OnAction = "StartChase"
    .Caption = "Send Chaser Msgs"
    End With
    End With

    End Sub

    and

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("AMG_Chaser").Delete
    End Sub

    The before_close event will delete the toolbar when you uncheck the add-in.

    Excel stores the toolbar settings to its xlb file when you quit Excel. When you start up Excel the next time, the toolbar will be there, so, the add-in will cause an error when trying to create a toolbar that already exists. You should delete the existing toolbar before you run the code that creates it.

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Assign OnAction to button in Add-In?

    Hans,

    I somehow managed to miss your followup post a month ago, when you posted it ( <img src=/S/blush.gif border=0 alt=blush width=15 height=15>).

    Just wanted to mention that I had already put code in to destroy the toolbar when the Excel app is closed.

    The actual add-in was deployed a few weeks ago, so far no problems. Thanks again for your help in getting me set right!

    Gary

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assign OnAction to button in Add-In?

    I don't want to find out the hard way so I thought I'd better ask:

    By using the _Open event and _Close event to deploy and delete a custom toolbar, What would happen if the spreadsheet or Excel did not close properly, assuming that work had been saved? I'm thinking power loss, computer lock up, accidental re-boot, etc. Just trying to prevent an issue before it happens...Murphy will enter the picture.
    - Ricky

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign OnAction to button in Add-In?

    If you look at Hans Pottel's code closely, you will see that the Workbook_Open event deletes the toolbar if it exists (with an On Error Resume Next before it to avoid raising an error if the toolbar doesn't exist), and after that creates it anew.

    Regards,
    Hans (not Pottel)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •