In some of my files, macros run automatically when you select a cell on the worksheet. For example, if you’ve bought a copy of my Data Validation Multi-Select Premium (DVMSP) kit, it shows a pop-up list when you click on a cell with a drop down list.
I’ll show you how to quickly change that, so the pop-up appears when you double-click, and you could make a similar change in other worksheet code.
Use a Double-Click
If you find that the DVMSP code is slow in a big workbook, or if you don’t want to see the pop-up list as you move around the workbook, you can make a simple change to its code.
Instead of showing the pop-up list when a cell is selected, change the code to run when a cell is double-clicked. That should speed things up a bit, and the pop-up list will only appear when you want it to.
Change the DVMSP Code
To make the pop-up list appear on a double-click, follow these steps to make a couple of simple changes to the code:
- On the sheet where you pasted the code, right-click the sheet tab, and click View Code.
- At the top of the code module, click in the blank row above the Private Sub Worksheet_SelectionChange line
- At the top right of the window, click the arrow in the Procedures drop down, and click on BeforeDoubleClick
- Select from the End Sub line, down to the Private Sub Worksheet_SelectionChange line
- Delete those lines
- Scroll down a bit, to the first “Select Case Target.Column” section
- Click in the blank line above that section
- In the blank line, type: Cancel = True (that turns off the default double-click action, such as entering the cell to edit it)
- Then, click the Save button, at the top right of the Visual Basic Editor window.
Get the DVMSP Kit
If you don’t have a copy of my DVMSP kit, you can learn more about it here. You can add this feature to your workbooks, and then use them yourself, or share them with your co-workers. They’ll be impressed!
Learn More About Excel Macros
Closes Sunday, Feb. 7th – Would you like to learn how to save time with Excel macros? Take a look at the free Mini Course on Macros & VBA — an excellent video series by Jon Acampora, from Excel Campus. Jon’s simple step-by-step approach makes it easy to follow along and learn, so get started now!
__________________
