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
526 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
  • 10
  • 4
  • 2
  • +2
21 Comments
 
LVL 11

Expert Comment

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

Author Comment

by:satmisha
Comment Utility
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
Comment Utility
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
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

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

Expert Comment

by:jkpieterse
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Expert Comment

by:jkpieterse
Comment Utility
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
Comment Utility
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
Comment Utility
Hi experts, please reply..
0
 

Author Comment

by:satmisha
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You cannot to my knowledge.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 174 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now