Change Excel VBA Code to Improve Speed

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.

Popup List on Click or Double-Click

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

Worksheet_SelectionChange code

  • At the top right of the window, click the arrow in the Procedures drop down, and click on BeforeDoubleClick

Worksheet_BeforeDoubleClick code

  • Select from the End Sub line, down to the Private Sub Worksheet_SelectionChange line
  • Delete those lines

Delete lines of code

  • 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.

Add Cancel = True code

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!


Popup List on Click or Double-Click

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *