Create Custom Ribbion - Basics 5

[MattGreer]

I have an Excel (Office 360) workbook with a lot of macros. Currently the macros are triggered by buttons on individual worksheets. I'd like to migrate many of them to a custom toolbar for this workbook.

In the past what I've noticed is that some Excel workbooks with a custom toolbar leave it behind... meaning the custom toolbar shows up (and obviously doesn't work) when I open a blank workbook or other spreadsheet. I want to avoid this of course.

I searched the forum but didn't find a basic how-to on this topic. I'd appreciate some help, either a step-by-step in general how to do this or a link to another website somewhere, on how to create a custom ribbon that will stay with the workbook no matter where it goes and the custom ribbon doesn't show up in other workbooks.

I realize this is probably a dumb question but, my google-fu is failing today and just about everything I search for doesn't sound like it fits this. I appreciate your patience and help!

edit: What google seems to always show me is how to add a custom ribbon, but it stays with every workbook you open, new or existing. I want the custom ribbon to stay with one and only one workbook and now keep showing up in other workbooks.


Thanks!!


Matt

 

[Andrzejek]

Here is some information on how to Customize the Ribbon in Excel:
[ul]
[li]add New Tab[/li]
[li]Add the commands to your Tab[/li]
[li]Rename the Tab and Group[/li]
[/ul]
I am sure you can create a simple macro to do all of this in Workbook_Open so you get your ribbon in this particular Workbook and not the others.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Matt,

So your objective is to have a workbook that...

In WORKBOOK_OPEN Event, ADDs certain BUTTONs and CONNECTs those buttons to certain procedures
In WORKBOOK_CLOSE event, DELETEs those BUTTONs

That's a plan.

A MAN A PLAN A CANAL PANAMA

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

There are custom UI editors, either free or paid. Some info and links you can find for instance here.

combo

 

[MattGreer]

Andrzejek, Thanks for that suggestion. But, isn't that procedure for adding a customized ribbon for ALL workbooks, not just the one you're working on?

SkipVought, that sounds about right! Thank you, that will definitely help with my google-fu. I guess in my mind I would have thought a custom ribbon would be, like, idk, it's own entity. It sounds like that's not the case and the ribbon must be created/destroyed (destroyed?) upon opening and closing the workbook.

combo, I'll check those out, thanks!



Thanks!!


Matt

 

[Andrzejek]

I've mentioned Workbook_Open, but forgot what Skip suggests: get rid of it in WORKBOOK_CLOSE

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Turns out, you cannot records a macro to add a new tab to a ribbon, etc.
Recorded macros are empty :-(

But, here is a nice discussion of How to add a custom Ribbon tab using VBA (or here) anyway and it looks promising

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Regarding Event Sequence, use the Workbook_BeforeClose Event to run any procedure(s) to delete the buttons and tabs and then SAVE.

But why not use a Dashboard sheet? Lots simpler than what you propose.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

Andrzejek, my bad, yes you did mention that! My apologies. And thanks for those links; I'll check them out shortly.

SkipVought, an interesting idea to use a dashboard. There's data entry on multiple different worksheets, so my thinking was making the macros available to the user (me until they fire me lol) no matter where s/he was. Perhaps I'm visualizing it incorrectly? What I think you mean is one worksheet with all the functions/buttons/automation on it, correct? If that's the case, I already have that. I'd prefer to activate the functions from... anywhere... If I misunderstand I'd appreciate your help in understanding!



Thanks!!


Matt

 

[SkipVought]

Well not knowing what you're functionally attempting to accomplish leaves me in the dark.

You wanted buttons moved from sheet to Ribbon. I guess I'm wondering, what do you gain?

I often put controls on my sheet. Some times my sheet controls were invisible and made visible/invisible and positioned by Selection Events on the Sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[strongm]

Excel still has toolbars ...

 

[MattGreer]

SkipVought,

So what this is is a customized front-end interface for a pipeline modeling program. What this does for me is allows me to 'expose' inputs and outputs for the software, rather than having to dig through all the menus and dialog boxes and stuff in the software itself. Excel is much better suited for displaying the results, with tables, charts, graphs, conditional formatting, etc.

Each worksheet in the workbook refers to different parts of the user interface, things like pipeline sizes, well production values, system boundary conditions, environmental conditions, and compositional analysis/setup. The worksheets interact to a certain extent through structured tables and formulas so, where it makes sense, where I'm looking at one piece of information I can see something related from another sheet.

The workbook has also been automated to run studies for me. Of particular interest are the pipeline sizes as I'm often tying to optimize those pipelines vs the expected flow through the lines. I can set up studies on one worksheet and trigger each of those studies, or all of them, using one or another macro button. I can also clear out the results and re-apply formatting if needed, via those macros.

Depending on what I'm doing, I might want to trigger some kind of action by Excel to send data into, or out of, the software. These functions are often duplicated on each sheet. I have to make space for the buttons and such, and I'm kind of thinking it might be nice to have the buttons removed and instead, use a toolbar or ribbon to activate those functions.

A lot of what goes on in the background, between Excel and the software, is written in python, if that matters.

strongm, I was somewhat aware of that, but thought toolbars were no longer en vogue, or possibly they no longer functioned/weren't available. What are your thoughts on toolbars vs the ribbon?

Thanks!!


Matt

 

[mintjulep]

How about a modeless form always on top.

https://www.mrexcel.com/board/threads/userform-always-on-top.386643/

 

[strongm]

>possibly they no longer functioned/weren't available

Definitely available, and definitely functional (albeit in a somewhat more limited fashion than in older versions of Office). And they pretend to be a ribbon by appearing on the Add-ins ribbon, in a section called Custom Toolbars.

>no longer en vogue

For a quick and dirty requirement such as the one you have outlined - grouping a bunch of macro buttons in one place - a custom toolbar seems like a reasonable solution

 

[MattGreer]

To all,

I don't really know what's best with regards to the user interface. There's things that are duplicated on each page albeit customized. Thinks like clearing out preexisting data and resetting formatting. Or making a connection to python (opening up a cmd window and setting the port number and so forth). It doesn't hurt anything to have buttons on each sheet that point to the same macro; I think part of what I'm trying to do is clean up each spreadsheet so that the real estate is dedicated more to the data rather than the functions. I want to minimize the amount of mouse use as well. You guys' advice is very much appreciated for anything and everything. Perhaps I should start a user interface discussion thread somewhere else?

With regards to the creation of a custom ribbon, thanks to Andrzejek his links led me to the Office RibbonX Editor which makes the creation and editing of a custom ribbon pretty straightforward. I tried it manually and left out a closing tag in the XML and you don't get any warnings or errors; it just doesn't work. I learned a good bit tho this is exactly what I was looking for.

Thanks to all!

Thanks!!


Matt

 

[MattGreer]

mintjulep it's funny you mention that. There's another software program that I use occasionally that has that built into their Excel reports and I was wondering how to build one. Thanks!!

Thanks!!


Matt