PDA

View Full Version : Adding a ribbon item...excel '07



jproffer
12-27-2008, 07:44 AM
Latest problem...

Our machine at work was just updated to Excel '07. I have an add-in that our dept. uses ALL the time and it would be much more handy to have to it as it's own ribbon item instead of buried in the "add ins" box.

I know there is software available to add the "old style" menu above the ribbon but I doubt the company is going to spend (and I'm SURE not going to spend on their behalf) the, even little bit of, money to get it.

Also, it's not the end of the world if it's not possible to programmatically add a ribbon item, but if anyone knows a way...has the code...knows where I can even begin to look for this needle in the haystack....any help would be greatly appreciated.

Thanks in advance :hi:

Bob Phillips
12-27-2008, 08:05 AM
It is not that hard, but you need to work with XML. Ken has the best intro IMO, read part 1 at http://excelguru.ca/blog/2006/11/20/modifying-the-ribbon-part-1-of-many/

Paul_Hossler
12-29-2008, 07:54 AM
Also, check out the

Office 2007 Ribbon UI (http://vbaexpress.com/forum/forumdisplay.php?f=96)

Forum here in VBAX

Paul

jproffer
12-29-2008, 11:45 AM
Yikes, lol

Looks pretty involved converting from the menu-maker I had in the previous version to the XML version I'm going to need now.

I'll have to read up on that later tonight...at first glance it looks like I'll have to completely rebuild the add-in...???...into a xlsm (or whatever it said..macro enabled workbook). Before I even begin to do this, I'll definatelty read up as much as possible.

Thank you for your help gentlemen

Paul_Hossler
12-29-2008, 07:47 PM
Not really to hard to add some simple ribbon controls to an XLSM and then save it as an add-in

The basic XLM is "pretty' straight-forward for just adding buttons, etc. Some of the more advanced controls can be tricky, and I haven't tried some of the more "pro" stuff.

Hardest part for the stuff I ever did, was picking the right picture for the button.

Post any questions here.

Paul

jproffer
12-31-2008, 06:31 PM
I can't even figure out where to start...lol.

In its present form, the menu is made from a looping code (below) that pulls from a worksheet within the add-in. (Quite an impressive piece of code...written by John Walkenbach).

I'm not even sure code started in previous version can have code in...XML, is it?

Finally, to top things off, I have excel XP at home, and obviously '07 at work, so anything I test has to wait until I'm there.

anyhow, ANY ideas to get me started to go from this:

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
Dim X As Long
On Error Resume Next
Dim counter As Long
counter = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
1 Set MenuSheet = Workbooks("Quality_Control_TRS.xla").Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu
' Variable for menu position
X = Application.CommandBars(1).Controls.count
' Set the main menu item position on menusheet cell C2
Workbooks("Quality_Control_TRS.xla").Worksheets("MenuSheet").Range("C2").Value = X + 1
' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
If counter = 1 Then
counter = counter + 1
GoTo 1
Else
End If
End Sub

to what I need would be greatly appreciated. I don't expect anyone to do it for me (although at this point I can't say I'd turn ya down :whistle: ), but any help or push in the right direction would be great.

Paul_Hossler
12-31-2008, 08:41 PM
Micro$oft spent billions of dollars and millions of person-hours to change the interface, just to give us all something to do in our spare time:banghead:

The little bit below does NOT even begin to scratch the surface.


Well, FWIW (bold, underlined, big type) when I had to convert an XLS from 2003 to 2007, I just started over with the design to use the UI and not to fight it

1. I went to

http://openxmldeveloper.org/articles/customuieditor.aspx

and got a copy of the CustomUI Editor. It's a little quirky, but works pretty well, and it's free. Also check out the links and comments


2. Created a new XLSX and saved it as a XLSM.

3. Used the editor to open it, and started with one of the Samples ("XL - Custom Tab")

4. It made some boiler-plate XML (the header and the 'customTab' below)

5. I added the customTab1 XML to create a demo XLSM for you.


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="customTab"label="Contoso"insertAfterMso="TabHome">
<group idMso="GroupClipboard" />
<group idMso="GroupFont" />
<group id="customGroup"label="Contoso Tools">
<button id="customButton1"label="ConBold"size="large"onAction="conBoldSub"imageMso="Bold" />
<button id="customButton2"label="ConItalic"size="large"onAction="conItalicSub"imageMso="Italic" />
<button id="customButton3"label="ConUnderline"size="large"onAction="conUnderlineSub"imageMso="Underline" />
</group>
<group idMso="GroupEnterDataAlignment" />
<group idMso="GroupEnterDataNumber" />
<group idMso="GroupQuickFormatting" />
<group id="customGroup1"label="Push These">
<button id="bRunSub1"label="One"size="large"onAction="onMyButton"image="about" />
<button id="bRunSub2"label="Two"size="large"onAction="onMyButton"image="add_file" />
<button id="bRunSub3"label="Three"size="normal"onAction="onMyButton"image="add_folder" />
<button id="bRunSub4"label="Four"size="normal"onAction="onMyButton"image="application" />
<button id="bRunSub5"label="Five"size="normal"onAction="onMyButton"image="break" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>


6. The customUI Editor menu buttons are

File Open (the XLSM)
File Save (the XLSM)
Add a icon (PNG files are best) to the 'container' for buttons, labels, etc.
Validate (XML is very fussy, spelling, UC/LC, etc.)
Generate CallBacks

imageMso= is to use a built in icon, and image= is to use one that you included (Add a icon) yourself.

7. You need the Callback to get the VBA that you want to past into a regular module. Each type of control has it's own calling format, including the Ribbon. Pasted this into the demo XLSM also


'Callback for customButton1 onAction
Sub conBoldSub(control as IRibbonControl)
End Sub
'Callback for customButton2 onAction
Sub conItalicSub(control as IRibbonControl)
End Sub
'Callback for customButton3 onAction
Sub conUnderlineSub(control as IRibbonControl)
End Sub
'Callback for bRunSub1 onAction
Sub onMyButton(control as IRibbonControl)
End Sub


Each type of control has it's own features, but the MSDN has some good background reference material, as does

http://www.rondebruin.nl/ribbon.htm

and the refenence XLD gave you


Have fun:beerchug:

Paul

jproffer
12-31-2008, 08:48 PM
yowza :wot

there's 3 more days of reading. Thanks so much for that. Like i said, I have to do all this at work, and I go back on friday so...I'll (hopefully) be able to DL the UIEditor there, and (hopefully) be able to try some of this at that time.

Thanks again, Paul

Paul_Hossler
12-31-2008, 08:53 PM
Boy, you're fast -- I wanted to double check the sample file, so I edited the earlier reply to add the file and you picked it up before I saved the edits

Paul

Paul_Hossler
12-31-2008, 09:03 PM
Also highly recommended -- both the complete books and the samples

Excel 2007 VBA Programmer's Reference
Written by John Green (greenj@bigpond.net.au?subject=Excel 2007 VBA Programmer's Reference), Stephen Bullen (stephen@oaltd.co.uk?subject=Excel 2007 VBA Programmer's Reference), Rob Bovey (robbovey@appspro.com?subject=Excel 2007 VBA Programmer's Reference) and Michael Alexander (mha105@yahoo.com?subject=Excel 2007 VBA Programmer's Reference)

http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm

http://www.oaltd.co.uk/DLCount/DLCount.asp?file=Excel2007VBAProgRef_ch14.pdf


RibbonX: Customizing the Office 2007 Ribbon (Paperback)

by Teresa Hennig (http://www.amazon.ca/exec/obidos/search-handle-url?%5Fencoding=UTF8&search-type=ss&index=books-ca&field-author=Teresa%20Hennig) (Author), Robert Martin (http://www.amazon.ca/exec/obidos/search-handle-url?%5Fencoding=UTF8&search-type=ss&index=books-ca&field-author=Robert%20Martin) (Author), Ken Puls (http://www.amazon.ca/exec/obidos/search-handle-url?%5Fencoding=UTF8&search-type=ss&index=books-ca&field-author=Ken%20Puls) (Author)


http://www.excelguru.ca/blog/2008/01/10/an-excerpt-from-ribbonx-customizing-the-office-2007-ribbon/

http://www.excelguru.ca/node/93


Paul

jproffer
01-01-2009, 06:00 AM
I'm not that fast. I guess the forum is set to auto-subscribe to any thread I start or reply to...which sends an email...and that email is a hotmail account which is linked to MSN messenger...which is always on, lol.

Anyhow, I hadn't copied anything from any of the replies yet. With all the links, I have enough reading for a week...which is good. I genuinely want to figure out how to do this.

Thanks again...

HAPPY NEW YEAR!!!:band: :bigdance2


Tomorrow when I get back to work, hopefully I can start the first of many...MANY rounds of testing.

jproffer
01-02-2009, 12:35 PM
The "really (so called) simple example" file you have just tells me that I'm completely...COMPLETELY over my head.

I can't even FIND the code that makes the ribbon addition when the WB opens, much less modify it to suit what I want(ed) to do.

I will admit that I haven't had a chance to read all the references yet...I will, and maybe that will point me in the right direction...but as it stands right now, our add-in is going to have to rest it's weary bones in the "Add-Ins" ribbon area.

As I said, I do fully intend to look into all of the links you posted and I appreciate you taking the time.

I can see now, that this isn't going to be a "come in one morning and have this done and ready to go in an hour or two" type of job.

Bob Phillips
01-02-2009, 12:37 PM
Have you opened the xlsm file in the Custom UI editor that Paul pointed you at?

jproffer
01-02-2009, 08:15 PM
No sir...I'm going to try to DL that tomorrow at work. I guess I was getting ahead of myself trying to open it "normally".

I'm reading some of the links, etc posted tonight.

Bob Phillips
01-03-2009, 05:16 AM
You can open the XML file separately, but you have to change the Excel file extension from xlsm to zip and open it with WinZip or some such program. You will see all of the files then.

jproffer
01-03-2009, 07:34 AM
Just to keep you both, sorta, up to speed...

I'm at work now, I installed the UIEditor and it seems to work great.

I started to modify the code for the ribbon and all was...WAS :cool: going well.

I changed the main tab name...save, close, open in excel...worked fine...go back to the UIEditor.

Changed some of the button names...save, close, open in excel, looks good.

Added another group with about 25 buttons....save, close, open in excel and.........nothing.

The custom tab is gone. So now I'm going to DL your "really simple example" and try again, being careful not to change ANYTHING except the labels and id's and see what happens.

I can see a very small, very dim light at the end of a very long tunnel, lol. Thanks to both of you.

After I get the tab to "look" like I want it to, then I have to make it actually DO something. I'm hoping I can add this file as an add-in and change the "onAction" for each button to call the code from the original add-in...?????... c'mon, make me smile, tell me I can do that...lol.

Anyow, thanks again...at least now I can actually see what you've been talking about and it's starting to make sense.

Bob Phillips
01-03-2009, 08:11 AM
Of course you can. As you can see, the XML has a n OnACtion property just as commandbars do.

jproffer
01-03-2009, 08:32 AM
Roger that. It looked similar...I was just hoping that it WAS similar.

OK, another idea (which, naturally, brings about another question):

Most of the (old) level 2 menu items have several level 3 items attached to them. Making a group for each level 2 item would be fine, but then each group would have up to 25...sometimes 30 buttons. Near as I can figure this would make the ribbon approximately 24 "monitors wide" ... :)

So I was looking at the stock ribbon in excel and some of the buttons have a small arrow, for instance, on the Home Tab, the Paste button...drops down to various specialty pasting choices.

So I'm wondering if I can make a dropdown button for each level 2 menu item, with their respective level 3 menu items below....???

Any ideas...and any idea what this special button might be called when coded.

A normal button is just:

<button

and then it goes into the ID and Label and what-not.

so this might be:

<dropbutton
'or
<dropdownbutton


and then even after that, how would I attach these sub menus to these "dropbuttons" or whatever they are called?

And again...broken record here, but I can't even express how much I appreciate both of your help.

jproffer
01-03-2009, 09:00 AM
OK, I've been looking some more and you can't change "group" to anything else, it has to stay "group" which is the labels at the bottom of the ribbon (I know I'm not telling you anything you don't know, lol...just talking it out).

So in reality, I only need one "group" with several drop downs similar to the "conditional formatting" button on the Home tab, would be ideal.

One button would say "Area Calculators" with all of the actual calculators listed below...another for "Volume Calculators" with calc's below, and so on.

I guess there's really no question here, per se. Just getting all these thoughts down before I forget :) .

Bob Phillips
01-03-2009, 11:11 AM
Why would you not have an Area Calculators group, and a Volume Calculators group, separately?

jproffer
01-03-2009, 11:56 AM
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.


<group>
<menu>
<button/>
<button/>
'and more buttons
</menu>
<menu>
<button/>
</menu>
'more menus
</group>


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.

jproffer
01-05-2009, 02:25 PM
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.

Bob Phillips
01-05-2009, 02:46 PM
The latter way is the correct way to do it.

Paul_Hossler
01-05-2009, 06:09 PM
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


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



Paul

jproffer
01-06-2009, 06:59 AM
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

Bob Phillips
01-06-2009, 07:30 AM
Post the offending workbook.

jproffer
01-06-2009, 07:42 AM
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).

Bob Phillips
01-06-2009, 08:07 AM
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.

jproffer
01-06-2009, 08:15 AM
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.

jproffer
01-06-2009, 08:48 AM
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

Bob Phillips
01-06-2009, 08:49 AM
I pressed the circle button. When I added ARunsub1 into onMyButton case statement, this is what I got

Paul_Hossler
01-06-2009, 09:00 AM
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

Paul_Hossler
01-06-2009, 09:05 AM
When you use a icon that is NOT a built in Office icon, you need to use image= and not imageMso, ex. image="Alice"


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



The callback should look something like this


'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



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

jproffer
01-06-2009, 09:07 AM
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.

jproffer
01-06-2009, 09:09 AM
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 (http://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.

Bob Phillips
01-06-2009, 09:25 AM
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



'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


to this



'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

jproffer
01-06-2009, 12:35 PM
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??

Paul_Hossler
01-06-2009, 01:07 PM
(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?


'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




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


Paul

Bob Phillips
01-06-2009, 01:10 PM
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?

jproffer
01-06-2009, 01:50 PM
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 :)

jproffer
01-06-2009, 07:22 PM
I do believe I've slipped into idiocy :)

I just got home with a (relatively) clear mind and I'm looking at the code...added the missing area calc stuff...looking at the callbacks and....

:doh: :doh: :doh:

WHATTTT???

When i said "the far menu items don't work at all"......yea dumba$$, you didn't make the callbacks yet for about the last 2/3 of the d#m% thing.

Anyhow, I'm working on it tonight, going to try to get some of this cleared up and I'll re-post tomorrow.

Also wanted to thank xld....AND Paul. I do appreciate BOTH of your help, and I didn't mean to leave one of you out before :). As I said, it was a long day...headache from looking at this thing...and I was ready to get home.

jproffer
01-06-2009, 10:15 PM
It seems everything is working. There's still ALOT of testing to do, naturally, but it seems to work.

One question:

Does anyone know why the message below would pop-up every time I open excel. It has to do with the new add-in workbook and it seems like it started when I changed it from xlsm to xlam format.

Paul_Hossler
01-07-2009, 12:48 PM
Did you put your addin in one of the trusted locations?

Office Button, Excel Options, Trust Center, Trust Center Settings, Trusted Locations

Paul

jproffer
01-08-2009, 09:27 AM
Thanks Paul, I'll check that out. In XP, I know it was there, but I never had to worry about it.

jproffer
01-26-2012, 12:31 PM
It's been 3 years since this all began.

As I read through this thread I was thinking "WOW, that jproffer guy is a real dumba....OHH that's me" :)

Just wanted to thank everyone on this thread AGAIN...your time and patience with the former "me" is greatly appreciated.