suppress unwanted routine

Brian F

Active Member
Aug 31, 2009
I have some code that causes any cell that contains a drop-down to increase in size (Private Sub Worksheet_SelectionChange(ByVal Target As Range) so I can see the choices when working in the field, however, any code I write that includes a range that contains a drop down menu will divert the procedure and run "Worksheet_SelectionChange". Is there some code I can put in my routine that will suppress "Worksheet_SelectionChange"?

Thank you in advance for any help!

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You can disable events in the subsequent code.

Application.EnableEvents = False

your code

Application.EnableEvents = True 'Always reset to True at the end..
Upvote 0
I work with this program in the field using a small screen and even with the increased size it is difficult to see the drop down choices. Without the increased size the program would be unusable.
Upvote 0
We're not suggesting that you remove the increase size feature.
We are suggesting two ways to avoid it when running your routine.

The preferred way would be to not select cells in your routine.

If that is not doable, you can suppress the SelectionChange event, which calls your increase size feature, with code like

Sub myRoutine()
    On Error Goto ErrorOut
    Application.EnableEvents = False

    Rem your existing code

    Application.EnableEvents = True
End Sub
But removing selection from your existing code is strongly prefered, not only does it avoid the increase size feature, but performance speed will be increased.
Upvote 0
Thank you to all. As you can see I'm on low ground heading up a steep learning curve. I figured out after my last post that it was the process of "selecting" (not the routine) was what was being suggested to curtail.

I had figured out that some things did not require that I "select" others seemed to require that I did and my knowledge is insufficient to determine the whys and whens. I inserted the "Application.EnableEvents = False" prior to lines where the problem arose and the "Application.EnableEvents = True" where it seemed appropriate and it seems to work. I will look at Mr. Rickson's code until I understand the difference.

It is so immensely helpful to have knowledgeable people leading the way. Rather than getting hung up and frustrated at each turn I can get help with the problems and continue to learn. I'm so excited to finally be learning VBA that I'm into one of those situations where hours pass like minutes.

Thank you all again.
Upvote 0
Glad to help out..

It will definately be worth your while to look into when/why/how to avoid select.

When turning off Events, it is IMPORTANT to always ensure they are turned back on when done.

A problem that can arise is if your code errors for any reason.
Say you turn events off
And subsequent code causes an error and goes into debug mode.
If you then just End the procedure, events are still Off.
So future selection changes will not fire the code..

if that happens, turn them back on in the VBA window
click View - Immediate Window
Type Application.EnableEvents = True

Mike's code is a nice way of handling that.
It ensures that when an error occurs, it goes to the end and turns events back on.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back