![]() 'If TRUE, the menu will pop up, if FALSE Excel's standard Set rIsect = Application.Intersect(Range("Area"), Target) 'the name "Area", the following line will trigger 'We check if the mouse right-click was in the (ByVal Target As Excel.Range, Cancel As Boolean) Below are the the 7 demo macros called by the menu. ![]() You can make as many menu items as you wish. "Procedure CreateShortcut.", vbCritical, "Error" 'Indicate a new "group" by inserting a line. 'Add menu item 2 using the same "recipe". 'functionality dynamically depending on other parameters. 'The point is simply that you can change the look and 'You can also let it be visible, but disable it by 'determine, if a menu item should be visible or not. 'a variable (here the non-declared "MyVar") can 'Below (as a comment) an example on how the value of 'There are thousands of FaceId icons, if you 'The procedure/macro to run if this menu item '(here "M") means, that you can press M instead of clicking. (Name:="MyShortcut", Position:=msoBarPopup, Temporary:=True) 'of command bars and name it "MyShortcut". 'We now add our 'command bar' to Excel's colllection If you want your popup menu to work automatically, when you open the spreadsheet, you can make an Auto_Open procedure like this: The spreadsheet is zipped, and you unzip it by right-clicking and doing whatever Windows suggests. Here you can put the kind of code that executes automatically, when certain events happen on this particular worksheet.Īnother option is to download a spreadsheet (Excel 2003) with the demo menu. In the image to the right the code sheet for Sheet1 has been selected. The macro "Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)" must be copied to the code sheet for the worksheet (or worksheets) where you want to use your own popup menu. If you use a small screen, some code lines may appear broken, but they will be okay when pasted. ![]() Just hightlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V). To test the example below, copy the macros "CreateShortcut" and "Dummy1" to "Dummy7" into a standard module in VBA. However it is pratical to make the menu automatically, when the spreadsheet is opened - more about this later. One, where you design the menu, and one that calls the menu, when you right-click. It requires only two macros to make the popup menu appear. the value of a variable can determine the status of a menu item. It can be hidden, shown, disabled or enabled runtime dictated by circumstance, i.e. With VBA you can easily make a popup menu (shortcut menu) that pops up, when you right-click, and replaces Excel's built-in menu for right-click.Įach menu item can call a macro or a standard function in Excel. Make your own popup menu in Excel with VBA
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |