Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create Custom Ribbion - Basics 5

Status
Not open for further replies.
MattGreer

MattGreer

Technical User
Joined
Feb 19, 2004
Messages
471
Location
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
There are custom UI editors, either free or paid. Some info and links you can find for instance here.

combo
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 [thumbsup2]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
>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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
thanhthanh12
  • Locked
  • Question Question
Create a application in word vba
Replies
3
Views
459
combo
combo
thanhthanh12
  • Locked
  • Question Question
How create a button to record a macro in userform
Replies
5
Views
652
thanhthanh12
thanhthanh12
Katto
  • Locked
  • Question Question
VBA create link to a module without running it
Replies
5
Views
523
SkipVought
S
poorulzz
  • Locked
  • Question Question
Can I create a button on VB form app to check-in or check-out documents on sharepoint?
Replies
1
Views
368
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top