Solved

IS it possible to have dynamic Menus in Excel 2010 using VBA like we do have in other Technologies ?

Posted on 2014-03-17
21
552 Views
Last Modified: 2014-04-27
Hi Team,
I need dynamic menus in excel 2010 using VBA like avaliable in any other technologies. I am newbi in VBA so have no idea whether it is possible or not, if it is possible then please help me to understand how it is possible.

for Example if user hover mouse over the screen menu should appear.
Hopefully looking forward good replies from experts
0
Comment
Question by:satmisha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 2
  • +2
21 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39936399
Where do you need that menu, is a right-click menu OK?
0
 

Author Comment

by:satmisha
ID: 39938251
I require navigation menu on the Excel worksheets. Which sits on top of all worksheet or constant for all of the worksheets.
0
 

Author Comment

by:satmisha
ID: 39938255
Just like we have any navigational menu in websites. I know excel has limited flexibility but if there is a way to have nice looking navigational menu then please let me know.

Hopefully looking forward to hearing form you.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39938685
Hi there,

While you could do this, it would take a bit of VBA programming, and IMHO the more code you write the more you open yourself to it breaking. Working on a single machine is fine, but I'm assuming with a menu system this will be used in various locations and Office configurations. That spells increased maintenance/testing time.

Would it be possible to just use a custom ribbon instead? If not, do you have any examples?

Regards,
Zack Barresse
0
 

Author Comment

by:satmisha
ID: 39947132
Thanks Zack. Here I am enclosing the image of the navigation menu as an example.
NavigationMenuExample-ScreenShot.jpg
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39947590
0
 

Author Comment

by:satmisha
ID: 39964724
Nop this is not what I want. But appreciate for your time and prompt reply.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39965784
Excel 2007 and up offer some ways to do what looks like what you need.

1. Adding a custom menu in 2003 style, which will appear as an entry on the add-ins tab of the ribbon (using the VBA Commandbars object)
2. Add a custom tab or a group to the ribbon with your menu (using RibbonXML)
3. Add a dropdown menu to the quick access toolbar (like Ron de Bruin's page shows)
4. Add an item to a right-click menu (using the VBA Commandbars object)

Which is it you would like to use? I expect #2 fits best.
0
 

Author Comment

by:satmisha
ID: 39984571
Thanks  jkpieterse,
I have gone through the options that you gave. One simple last question related to same that I am designing dashboard like application, for that I require menus one way is to put flashy background then few images and on click of those images I could achieve functionality like show and hide different worksheet tabs.

Whereas I was looking dynamic menus for this task. If I browse my mouse over on I could get different menu option like:

Menu1
             SubMenu11
             SubMenu12
             Submenu13

Is this possible through options that you suggested above ?
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39986185
I'm not sure I understand what you want to happen. Do you want a menu to appear when you move your mouse over an object on a worksheet?
0
 

Author Comment

by:satmisha
ID: 39986990
Yes, kinda like that. We are not using any ribben control.

As of now we have placed few images on worksheet Top and on click of those images we do hide\unhide tabs. We have around 10 tabs having different dashboard kind stuff.

for example one image indicate employee another department, when user clicks on employee image we unhide employee tab similarly when user clicks on department tab we unhide department tab and hide rest.

Is there any better way to achieve that ?
0
 

Author Comment

by:satmisha
ID: 39992824
Hi experts, please reply..
0
 

Author Comment

by:satmisha
ID: 39997522
Hi Expert.. Please reply, I am looking forward to hearing from you.
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 174 total points
ID: 40021853
Note that excel is not other applications and what is possible is limited by excel.  As I see it you have two options mentioned earlier:

1. Use a pane on the ribbon either 'deleting' the original ribbon and leaving your own or more likely leave the default ribbon and add your own tab.  Buttons etc on your tab CAN be modes to be enabled/visible etc according to your own criteria although this does take more work.

2. Add a customised menu to the context menu, (right click menu) ... again bits can be enabled/disabled according to your needs.

Note however the dynamic element takes more coding but is possible ... the choice as they say is yours.

Chris
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40022935
I don't believe that you can achieve flyout menus of the type shown in your browser screenshot.  Those are Active Server Pages/ JavaScript / Browser technologies and I doubt you'll get Excel to mock them up.

The ribbon is the UI for Office, for better or worse.  It can be hidden, it can be set to auto-hide, so that the majority of it only drops down when the mouse ranges up there, or clicks on a Ribbon tab.  Within the Ribbon, you can create dropdown items, as well

I am newbi in VBA

This is one of the more complex things there are to muck with.  You will need to use the Ribbon, and then muck with the RibbonXML.  The following example is from MS Access and NOT Excel
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="onRibbonLoad">  
<ribbon startFromScratch="false" >
        <tabs>
            <tab id="RNDT" label="RNDT">
                <group id="PrintFind" label="Print/Find/Select/Copy" autoScale="true">
                    <control idQ="PrintDialogAccess" visible="true"/>
                    <control idQ="ZoomFitToWindow" visible="true"/>
                    <control idQ="FindDialog" visible="true"/>
                    <control idQ="FindNext" visible="true"/>
                    <control idQ="SelectAllRecords" visible="true"/>
                    <control idQ="Copy" visible="true"/>
                </group>
                <group id="SortFilter" label="Sort/Filter" autoScale="true">
                    <control idQ="SortUp" visible="true"/>
                    <control idQ="SortDown" visible="true"/>
                    <control idQ="FilterBySelection" visible="true"/>
                    <control idQ="FilterToggleFilter" visible="true"/>
			  <control idQ="FilterClearAllFilters" visible="true"/>
                </group>
                <group id="FormSelect" label="Select Form" autoScale="true">             
	    		<splitButton id="spBForms1" size="large" >
                    <button id="Dummy" label="Select a Form"  imageMso="ViewsFormView" />
                    <menu id="sbMnuForms1">                        
                       <button id="frmStartupScreen" label="Startup Screen"  imageMso="ViewsFormView" onAction="onOpenForm" visible = "true" />
                       <button id="frmDataEntry" label="Tubular" imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true"/>
                       <button id="frmChicsan" label="Chicsan"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmEngProject" label="Eng Project"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmFieldReport" label="Field Report"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmExcelLikeView" label="Advanced Search"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmCertStatus" label="Cert Status"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmBilling" label="Billing"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />                    
                    </menu>
                  </splitButton>
                <button id="ResetRibbon" label="Reset Ribbon" getImage="onGetImage" showImage="true" showLabel="false" onAction="onResetToolbars"/>
                <button id="Editor" label="Open VBA" getImage="onGetImage" showImage="true" showLabel="false" onAction="OpenCode"/>
	            <button id="OpenExcel" label="Open Excel" imageMso="FileSaveAsExcel97_2003" showImage="true" showLabel="false" onAction="OpenExcel"/>
                </group>
                <group id="Reports" label="Reports" autoScale="true">
               <control idQ="PrintPreviewClose" visible="true"/>
		</group>
            </tab>
        </tabs>  
</ribbon>
</customUI>

Open in new window


This is the bit that creates the dropdown button
	    		<splitButton id="spBForms1" size="large" >
                    <button id="Dummy" label="Select a Form"  imageMso="ViewsFormView" />
                    <menu id="sbMnuForms1">                        
                       <button id="frmStartupScreen" label="Startup Screen"  imageMso="ViewsFormView" onAction="onOpenForm" visible = "true" />
                       <button id="frmDataEntry" label="Tubular" imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true"/>
                       <button id="frmChicsan" label="Chicsan"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmEngProject" label="Eng Project"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmFieldReport" label="Field Report"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmExcelLikeView" label="Advanced Search"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmCertStatus" label="Cert Status"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />
                       <button id="frmBilling" label="Billing"  imageMso="ViewsFormView" onAction="onOpenForm"  visible = "true" />                    
                    </menu>
                  </splitButton>

Open in new window


But you are looking at messing with the RibbonXML, which is complex and not really well-documented. Each of the onAction items are VBA sub procedures that then execute various functions.

You can customize the Ribbon, and Excel's is easier than Access's, but it is not for the faint of heart nor for the newbie -- and it won't be a mouseover flyout.
0
 

Author Comment

by:satmisha
ID: 40026265
I really appreciate for your response, what you have jotted down above.

Let me clear my requirements:

1. I am not using ribbon control.
2. I am also do not want to use context menu (Right Click) way.

I need web page kind of menus the way we do have in our webapplication i.e. when user mouse over them it comes down like combo box.

Q. Can I achieve this with the help of VBA, I appreciate if you could provide some sort of sample on this ?

Looking forward to hearing from you.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 86 total points
ID: 40026384
You cannot to my knowledge.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 174 total points
ID: 40026562
I am not using a ribbon control ... it cannot be done without ... With the help of vba you can use the ribbon or context menus

Without using the ribbon you simply cannot do what you ask in any form

Chris
0
 

Author Closing Comment

by:satmisha
ID: 40026621
I guess experts have provided the right opinion. I am sitting on dead end and asking for the way.. seems like I need to re-think on requirement.

I appreciate for experts and there view. Thanks a lot.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question