Adding a ribbon item...excel '07

For those 2 that would work fine, as there are only 7 and 5 buttons for each group, respectively. However for another group called "Lab Calculators" there are about 20 buttons. Having all the buttons visible all the time tends to make the ribbon terribly long. Also, there would be many groups like this one (with 20 or more buttons) and would just add to the length problem.

Since the last post, I think I found what I wanted though. A "menu" :) . I had tried that before (or so I thought) and it didn't work right, but it does now.

[VBA]
<group>
<menu>
<button/>
<button/>
'and more buttons
</menu>
<menu>
<button/>
</menu>
'more menus
</group>
[/VBA]

So far this is doing what I want to do. There are several groups of calculators (various types...area, lab, volume, surface area, etc.) and a group heading of "Calculators" with one menu for each one is going well.....so far. This is still the verrrrrrry early stages, and I'm sure to have more questions as time goes on. Right now I'm just working on the look of the thing, lol...it doesn't actually DO anything yet so when I get to that point, I'll have many questions for you.

OK...I think I've got it the way I want it to look...but that's just in looks alone. :) Now I need for it to actually DO something...

.....first semi-educated guess:

In the "OnAction" attribute, I would simply put the Add-in WB name, and the name of the procedure?? Similar to calling code from an outside WB??

Also, would it make it any easier if I just moved all of the modules into the new WB (the one with the code for the ribbon)? Wouldn't I then be able to call just the procedure name with the OnAction attribute, without the WB name?

I could be way off base with any or all of this...I'm basically guessing right now.

The latter way is the correct way to do it.

Look at the Menu Control. It's sort of like a drop down list of buttons

I added it to the example, and you can use the CustomUI editor to look at it

[vba]
<group id="grpMenus"label="Areas and Things">
<menu id="mnuArea"
label="Areas"
size="large"
imageMso="_3DTiltDownClassic"
screentip="Calculate frequently used areas">
<button id="bASquare"
label="Square"image="Alice"
onAction="CalcPressed"
/>
<button id="bATriangle"
label="Triangle"image="Asok"
onAction="CalcPressed"
/>
<button id="bACircle"
label="Circle"
onAction="CalcPressed"
/>
<button id="bAParallelgram"
label="Parallelgram"
onAction="CalcPressed"
/>
</menu>
<menu id="mnuVolumes"
label="Volumes"
size="large"
image="Boss"
screentip="Calculate frequently used sort volumes">
<button id="bVSquare"
label="Square"image="Bob"
onAction="CalcPressed"
/>
<button id="bVTriangle"
label="Triangle"image="Dilbert"
onAction="CalcPressed"
/>
<button id="bVCircle"
label="Circle"
onAction="CalcPressed"
/>
<button id="bVParallelgram"
label="Parallelgram"
onAction="CalcPressed"
/>
</menu>
</group>

[/vba]

Paul

OK...here's where I'm at today.

I started entering the onAction attributes and on ALL of them so far, I get the following error:

"Wrong number of arguments or invalid property assignment"

Now, just for the sake of complete detail...here's what I did:

- Moved all of the modules and all of the userforms into the new WB.

- Assigned a couple of the more commonly used procedures to their respective buttons on the ribbon using just the procedure name I.E. - "Start_Test", "Multiple_Print", and so on, you get the idea. These are the names exactly as they appear in the code modules..."Sub Start_Test", etc.

Any idea what the error is about??

Thanks so much, in advance

Post the offending workbook.

Here it is...

I know some of the references as you go deeper into the code still have the old WB name and won't work, but the userforms should still come up with the commands I assigned (or so I thought).

Couple more things I wanted to add...I do understand that msoBoss, and the other similar names aren't valid icon images :) . I think I changed all of them to mso in anticipation of the end result, when I was stuck and just wanted to make SOME kind of progress.

The only ones that have the correct procedure assigned right now are the 4 large icons on the left of the ribbon (top 4 buttons in the xml code).

I don't get that problem. For me, I get the message 'I don't know what to do with ARunsub1' etc, and I get that because the onMyButton sub only looks for b subs in Control.Id.

I dunno which button you pushed. I may have edited my last post too late...only the first 4 are (what I thought was) done.

The "I don't know what to do with...." is part of the original code from the WB when I downloaded it from here, before I started changing it.

I think in the modules it would be Module1.

Little bit of an update:

I had forgot to put the callbacks into a module.

HOWEVER :)

1. When I click generate callbacks in the UIEditor, it doesn't generate all of them. I don't know if it should or not, but it doesn't.

2. I pasted some of the callbacks (the ones that it DID make) and called the procedures from there and it still doesn't work.

below is the newest version

I pressed the circle button. When I added ARunsub1 into onMyButton case statement, this is what I got

Quote:

---

Originally Posted by jproffer

2. I pasted some of the callbacks (the ones that it DID make) and called the procedures from there and it still doesn't work.

---

I think it only make one callback for each different onAction sub, so if you use the same onAction for multiple controls (like I did), you only get one callback sub

Paul

When you use a icon that is NOT a built in Office icon, you need to use image= and not imageMso, ex. image="Alice"

[vba]
<menu id="menu3"label="Volume Calculators"size="normal"imageMso="DatabaseCopyDatabaseFile">
<button id="VRunSub1"label="Cone"onAction="OnMyButton"imageMso="Alice" />
<button id="VRunSub2"label="Cube"onAction="onMyButton"imageMso="_3DStyle" />
<button id="VRunSub3"label="Cylinder"onAction="onMyButton"imageMso="Bob" />
<button id="VRunSub4"label="Pyramid"onAction="onMyButton"imageMso="Boss" />
<button id="VRunSub5"label="Sphere"onAction="onMyButton"imageMso="Dilbert" />
</menu>
[/vba]


The callback should look something like this

[vba]
'Callback for onMyButton onAction
Sub onMyButton(control As IRibbonControl)
Select Case control.ID '<---------------- the button id
Case "ARunSub1"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub2"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub3"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub4"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub5"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub6"
MsgBox "Run the sub for the button " & control.ID
Case "ARunSub7"
MsgBox "Run the sub for the button " & control.ID
Case Else
MsgBox "I don't know what to do with " & control.ID
End Select
End Sub
[/vba]


So what happens is that

1. The Ribbon loads and shows
2. You click on Areas, and the menu shows
3. You click on (e.g.) "Circle" which calls it's onAction = "OnMyButton"
4. OnMyButton gets the control that you clicked passed to it (control = the ID in your XML, "ARunSub1")
5. The Case in OnMyButton sub matchs the ID = "ARunSub1" and executes the VBA (a MsgBox in this case)

So you can use the same callback sub for multiple buttons. Suggestion is to name the controls (the id) with more meaningful names, like bCircleArea, bCircleVol, etc.

Remember the XML is case sensitive

Paul

Quote:

---

I think it only make one callback for each different onAction sub, so if you use the same onAction for multiple controls (like I did), you only get one callback sub

---

That makes sense.


xld, would you post the code that got you to that point? Maybe that example will clear things up for me and I can make the others to follow suit.

At this point I can't get my head wrapped around why we need a callback in the regular module and why we can't just "onAction" directly from the xml to the modules.

Quote:

---

When you use a icon that is NOT a built in Office, you need to use image= and not imageMso, ex. image="Alice"

VBA:
<menu id="menu3"label="Volume Calculators"size="normal"imageMso="DatabaseCopyDatabaseFile"> <button id="VRunSub1"label="Cone"onAction="OnMyButton"imageMso="Alice" /> <button id="VRunSub2"label="Cube"onAction="onMyButton"imageMso="_3DStyle" /> <button id="VRunSub3"label="Cylinder"onAction="onMyButton"imageMso="Bob" /> <button id="VRunSub4"label="Pyramid"onAction="onMyButton"imageMso="Boss" /> <button id="VRunSub5"label="Sphere"onAction="onMyButton"imageMso="Dilbert" /> </menu>

VBA tags courtesy of www.thecodenet.com

---

Yea I had just changed them because eventually they will be built in icons, and I wasn't making any REAL progress....so I thought I'd make some BS progress to at least be doing something that will eventually be useful.

Quote:

---

Originally Posted by jproffer

That makes sense.

xld, would you post the code that got you to that point? Maybe that example will clear things up for me and I can make the others to follow suit.

At this point I can't get my head wrapped around why we need a callback in the regular module and why we can't just "onAction" directly from the xml to the modules.

---

The callback is essentially the onAction macro, genearting callbacks generates a skeletal procedure foir you.

All I did was change this

[vba]

'Callback for bRunSub1 onAction
Sub onMyButton(control As IRibbonControl)
Select Case control.ID
Case "bRunSub1"
Call sub1
Case "bRunSub2"
Call sub2
Case "bRunSub3"
Call sub3
Case "bRunSub4"
Call Sub4
Case "bRunSub5"
Call Sub5
Case Else
MsgBox "I don't know what to do with " & control.ID
End Select
End Sub
[/vba]

to this

[vba]

'Callback for bRunSub1 onAction
Sub onMyButton(control As IRibbonControl)
Select Case control.ID
Case "ARunSub1"
Call Circle_A_UF
Case "bRunSub1"
Call sub1
Case "bRunSub2"
Call sub2
Case "bRunSub3"
Call sub3
Case "bRunSub4"
Call Sub4
Case "bRunSub5"
Call Sub5
Case Else
MsgBox "I don't know what to do with " & control.ID
End Select
End Sub[/vba]

OK...here we go again

Believe it or not, even though I keep changing the WB and reposting it, I'm trying to cause you LESS work and hassle, not more...

but here is yet another version of the WB.

Some of the buttons are working (save, print, close...print multiple...all of the lab calculators) while some are not (the area calculators error out with "sub or function not defined", further down the line in games, dev. tools, and such MOST of them don't work at all)

I still haven't finished the deeper parts of the code that actually do the work, but I think the userforms should open regardless. (fair warning: some of the buttons don't have userforms...they just go to work without warning or prompt, but there's nothing terrible going to happen either way)

I can't seem to figure out why some do and some don't. Any thoughts??

Quote:

---

(the area calculators error out with "sub or function not defined", further down the line in games, dev. tools, and such MOST of them don't work at all)

---

Some of the Area Subs are not defined, and that's the reason for the error in that area. Was there something else?

[vba]
'Callback for ARunSub1 onAction
Sub AreaMyButton(control As IRibbonControl)
'On Error Resume Next
Select Case control.ID
Case "ARunSub1"
Call Circle_A_UF
Case "ARunSub2"
Call Hexagon_A_UF
Case "ARunSub3"
Call Octagon_A_UF
Case "ARunSub4"
Call Parallel_A_UF
Case "ARunSub5"
Call Rectangle_A_UF
Case "ARunSub6"
Call Trapazoid_A_UF
Case "ARunSub7"
Call Triangle_A_UF
End Select
End Sub
[/vba]


[vba]
Sub Rectangle_A_UF()
On Error Resume Next
UserForm6.Show
End Sub
Sub Circle_A_UF()
On Error Resume Next
UserForm7.Show
End Sub
Sub Triangle_A_UF()
On Error Resume Next
UserForm8.Show
End Sub
Sub Hexagon_A_UF()
On Error Resume Next
UserForm9.Show
End Sub
Sub Octagon_A_UF()
On Error Resume Next
UserForm10.Show
End Sub
[/vba]

Paul

Quote:

---

Originally Posted by jproffer

OK...here we go again

Believe it or not, even though I keep changing the WB and reposting it, I'm trying to cause you LESS work and hassle, not more...

but here is yet another version of the WB.

Some of the buttons are working (save, print, close...print multiple...all of the lab calculators) while some are not (the area calculators error out with "sub or function not defined", further down the line in games, dev. tools, and such MOST of them don't work at all)

I still haven't finished the deeper parts of the code that actually do the work, but I think the userforms should open regardless. (fair warning: some of the buttons don't have userforms...they just go to work without warning or prompt, but there's nothing terrible going to happen either way)

I can't seem to figure out why some do and some don't. Any thoughts??

---

Did youo check-out my previous response?

OK...rookie mistake on the Area_Calc stuff. :doh: :doh:

I'll chech it out at home tonight and get back to ya....thanks again xld :)