Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is this possible for Excel 2007? (2007)

    Greetings,

    Well, the below code was written for Excel 2003. Basically, it created a dropdown menu, that always was placed on the bottom of the toolbar at the left.

    Well, I moved up to Excel 2007, and the code is not doing that task any longer. Is this possible in the 2007? any thoughts on how to accomplish the same concept?

    Thanks,
    Brad


    Private Sub Workbook_Activate()

    On Error Resume Next
    Application.CommandBars("CR Actions").Visible = True
    ' Worksheets("Workbook Contents Page1").Activate
    End Sub

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

    Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.CommandBars("CR Actions").Visible = False
    End Sub

    Private Sub Workbook_Open()
    Call CreateVariousDropdown
    End Sub

    Public Sub CreateVariousDropdown()
    Dim cbr As CommandBar
    Dim cbp As CommandBarPopup
    Dim cbb As CommandBarButton

    Set cbr = Application.CommandBars.Add("CR Actions")

    cbr.Position = msoBarTop
    Set cbb = cbr.Controls.Add(msoControlButton)
    With cbb
    .Caption = "CR Actions"
    .OnAction = "Select_Form"
    .Style = msoButtonIconAndCaption
    .FaceId = 2950
    End With

    Set cbp = cbr.Controls.Add(msoControlPopup)
    cbp.Caption = "Various"

    Set cbb = cbp.Controls.Add(msoControlButton)
    With cbb
    .Caption = "CR Sort"
    .OnAction = "CRSort"
    .Style = msoButtonIconAndCaption
    .FaceId = 2174
    End With

    Set cbb = Nothing
    Set cbp = Nothing
    Set cbr = Nothing
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this possible for Excel 2007? (2007)

    Your code will not work in Excel 2007 as the ribbon technology is entirely different to toolbars and menus. You can consider it a brand new object which is under heavy lock and key. The ribbon is a no go area for the average mortal working in Excel. That is why we have been given a tiny little toobar called the Quick Access Toolbar (QAT) which we can use to add or remove buttons at our convenience. I must admit that I am unsure if the QAT can be programically altered. (I have not experimented with this yet, and I cannot recall any questions being asked in the forum about it???) (Hey - that is a good question! Can it be programically altered?) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Coming back to the ribbon...

    There is a way to alter the ribbon, but it is a fairly compicated procedure. For more details see these threads, and also Jan Karel <!profile=Pieterse>Pieterse<!/profile> webpage. (If I can recall, he has an article about the ribbon too...)

    See post <post#=747,766>post 747,766</post#> - Modifying ribbon groups
    Also post <post#= 741,393>post 741,393</post#> - Customising the QAT and the Ribbon
    And Jan Karels site... http://www.jkp-ads.com/index.asp
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    Your toolbar DOES work, but is cunningly hidden on the addins tab of the ribbon.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this possible for Excel 2007? (2007)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> - I only see that now too!!!!! Cunningly hidden is right?!

    So sorry for my false reply bradjedis. I did not realise that old 2003 code still creates the add-in tab and sets up the buttons. I feel so embarressed!!!

    TX for that heads up Jan Karel.
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this possible for Excel 2007? (2007)

    Jan Karel,

    In your opinion, how long is VBA for Excel still going to be around?? It seems with Office 2007 that the programming side is tending towards XML. What have you heard in your circles and through your contacts about this??

    Just out of curiosity.
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    Edited by HansV to break extremely long lines

    You could use this ribbon XML to create your own tab on the ribbon, with two groups and each group having one button:

    <pre><customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
    <tabs>
    <tab id="rxCRActions" label="CR Actions">
    <group id="rxgrpActions" label="CR Actions">
    <button id="rxbtnCRActions" label="CR Actions"
    size="large" onAction="rxbtnCRActions_Click"
    imageMso="AcceptInvitation" />
    </group>
    <group id="rxgrpVarious" label="Various">
    <button id="rxbtnCRSort" label="CR Sort"
    size="large" onAction="rxbtnCRSort_Click"
    imageMso="AdpOutputOperationsSortAscending" />
    </group>
    </tab>
    </tabs>
    </ribbon>
    </customUI></pre>

    Then use this code to handle the clicks:

    <pre>Option Explicit

    'Callback for rxbtnCRActions onAction
    Sub rxbtnCRActions_Click(control As IRibbonControl)
    MsgBox "CR actions"
    End Sub

    'Callback for rxbtnCRSort onAction
    Sub rxbtnCRSort_Click(control As IRibbonControl)
    MsgBox "CR Sort"
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this possible for Excel 2007? (2007)

    <hr> I must admit that I am unsure if the QAT can be programically altered. (I have not experimented with this yet, and I cannot recall any questions being asked in the forum about it???) (Hey - that is a good question! Can it be programically altered?) <hr>

    I have tested this and AFAI(can see), it cannot (I hesitate to say) be programmed. (Geez...I get a shiver down my spine when I say "it cannot". Excel has proven me wrong sooo many times that I fear to say it!!!)

    I recorded a macro and added a button to the QAT. But when I looked at the code all I saw was Sub Test and End Sub! Absolutely nulla in the body of the procedure! I also ran a search in the Object Browser and came up enpty handed with any objects or properties for the QAT.

    Cheers
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    Ron de Bruin has written some stuff on the QAT:
    http://www.rondebruin.nl/imageqat.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    <hr>In your opinion, how long is VBA for Excel still going to be around??<hr>
    I'm restricted by NDA on matters like these, but you can rest assured VBA is not going away anytime soon. I'm talking more than 5 years...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this possible for Excel 2007? (2007)

    Thats good to know! TX
    Regards,
    Rudi

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    Wow, I did not realize the difference...


    Jan,
    How ....where.... does this code go? The other code was in the "this workbook" area.



    Thanks,


    Brad

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this possible for Excel 2007? (2007)

    The VBA code goes in a normal module. The XML code needs to be inserted in a very special way. The easiest method is by downloading a free tool:

    http://openxmldeveloper.org/articles...mUIeditor.aspx
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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