suppress unwanted routine

[Brian F]

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!

 

[mikerickson]

Its sounds like the other routine needs to not select cells. Selecting is rarely needed.

 

[Jonmo1]

You can disable events in the subsequent code.

Application.EnableEvents = False

your code

Application.EnableEvents = True 'Always reset to True at the end..

 

[Jonmo1]

Its sounds like the other routine needs to not select cells. Selecting is rarely needed.

What he said

 

[Brian F]

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.

 

[mikerickson]

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

ErrorOut:
    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.

 

[Brian F]

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.

 

[Jonmo1]

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.

 

[mikerickson]

It ensures that when an error occurs, it goes to the end and turns events back on.

The dance can get a bit complicated if myRoutine calls another sub and the error occurs in that sub.