Is this possible for Excel 2007? (2007)

Topic

New Reply

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

Reply | Quote

Replies

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?)

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 Pieterse webpage. (If I can recall, he has an article about the ribbon too…)

See post post 747,766 – Modifying ribbon groups
Also post post 741,393 – Customising the QAT and the Ribbon
And Jan Karels site… http://www.jkp-ads.com/index.asp%5B/url%5D

Reply | Quote

[indent]

---

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?)

---

[/indent]

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

Reply | Quote

Ron de Bruin has written some stuff on the QAT:
http://www.rondebruin.nl/imageqat.htm%5B/url%5D

Reply | Quote

Your toolbar DOES work, but is cunningly hidden on the addins tab of the ribbon.

Reply | Quote

– 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.

Reply | Quote

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:

Then use this code to handle the clicks:

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

Reply | Quote

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.

Reply | Quote

[indent]

---

In your opinion, how long is VBA for Excel still going to be around??

---

[/indent]
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…

Reply | Quote

Thats good to know! TX

Reply | Quote

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

Reply | Quote

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/customUIeditor.aspx%5B/url%5D

Reply | Quote