As ever, you use this free program at your own risk.
Feel free to use the free software on this site but please honor the copyright.
This page describes how to add items to the right click context menu in
Excel™. We will also provide 4 procedures that can be added to the right
click menu.
These are:
|
ShowOnly |
This will show only the rows on the worksheet that contain the same value as the ActiveCell in the same columns. |
|
ShowAll |
This resets the worksheet to show all the rows. |
|
HideAll |
This will hide all the rows on the worksheet that contain the same value as the ActiveCell in the same columns. |
|
DeleteHidden |
This will delete, after a positive confirmation, all the hidden rows on the worksheet. |
These procedures are fully functioning procedures. You can chose your own procedures if required. You can also add additional command buttons if needed.
It is important to note that the code, as delivered, will only execute on pages where the provided, accompanying worksheet event code has been added.
The code is, essentially, two separate components. The top part of the code should be "cut" out of the module and "pasted" into the worksheets that you want to use the 'right click menu' items on.
The second part is the "RightClickProcess" module. This module contains all the subroutines , ShowOnly, ShowAll, HideAll, DeleteHidden, RightClick, and RightClickOff subroutines. Additionally it contains one function (Check4Header).
The menu items will only be added to columns and rows if they contain data. This is intentional.
As provided, the code assumes that row 1 is a title line if the data in row 2 is either:
- A date format and the same column in row 1 isn't.
- A numeric format and the same column in row 1 isn't. or
- The text in row 1 is bold.
Obviously you can change any of the criteria that determines a header line but we think this selection, while not perfect, is adequate.
To install the Module, RightClickProcess into a spreadsheet follow the following process.
Step 1
Download the RightClickProcess.bas file from the website. This is available by clicking the button below.
Be sure to make a note of the directory where the downloaded file was saved.
Step 2
Open the target workbook. Make sure that the workbook is macro enabled. It should have an XLSM extension. If the workbook is not macro enabled, the code will not work.
Step 3
Open up the "Developer" tab from the Excel ribbon. If the "Developer" tab is not displayed then please refer to our webpage of how to activate the developer tab which can be navigated to by clicking the button below
Step 4
In the left hand panel, select the "Modules" folder icon and click the right-hand mouse button. Click on "Import File..." option.
When the 'file select' dialog opens, navigate to the folder where "RightClickProcess.bas" file was saved and click on the "RightClickProcess.bas" file. This will create the "RightClickProcess" module in the workbook.
Step 5
Click on the RightClickProcess module, then left click and select "View Code". This will open the module's code in the right-hand window.
Step 6
Select the first two subroutines (Worksheet_BeforeRightClick and Worksheet_Deactivate). Select the entire code in these routines. Cut(Ctrl+x) the code and delete the comments around these two pieces of code.
Step 7
Select the worksheet that you want to add the code too and right click on the worksheet. Select "View Code" and when the worksheet code opens in the right-hand panel, "Paste"(Ctrl+v) the previously copied code into the worksheet code.
Step 8
Repeat Step 7 for all the worksheets that you want to use this facility on.
Save the workbook.
That is all there is to it.
For an instructional video describing the process detailed above please click the button below.
This material has been used successfully by Abbydale Systems LLC. and to the best of our knowledge this material and any system(s) of which it is a part are operational as of the service level or date stated in the body of this material (if so stated). However, NO warranty is given or implied as to the accuracy of this material or any related material or systems, and no responsibility is assumed for any effect or modification directly or indirectly caused by the use of this material.
It is the responsibility of any user of this material to evaluate its usefulness to the user's environment.
Abbydale Systems LLC. does not guarantee to keep this or any related material current, nor does it guarantee to provide any corrections or extensions described by any users of this material or any corrections or extensions made in the future by Abbydale Systems LLC. itself.
