PDA

View Full Version : Ignoring a subroutine



JG4life
03-31-2006, 10:40 AM
I recently had a post asking for help with a bit of code and had such success I figured I would try one more time.

I have created a the following sub routine



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'On Error GoTo Finished

If Target.Row >= 8 And Target.Row <= 32 Then

If Target.Address = ("$B$" & Target.Row) Then
frmCalendar.Show
End If

Else
End If

Finished:

End Sub


This code is activated everytime a user selects a cell in the B column and brings up the calendar. However, there are other sub routines that also affect cells in column b and when they are run, it also runs the above subroutine. Is there a way I can code it in that when the other subroutines are run, it ignores the one above? I only want the above subroutine to run when I "click" in the cell, not when the focus is brought to a cell with another subroutine.

I am thinking since there is a way to supress warning screens while executing code, there is a way to supress subroutines from running while another is being executed.

Sean

xld
03-31-2006, 10:48 AM
In your other routines, start with


Application.EnableEvents = False


and at the end, reset back to True

mdmackillop
03-31-2006, 10:49 AM
You disable "events" using EnableEvents as follows

Sub MoveToCol()
Application.EnableEvents = False
[B12].Select
Application.EnableEvents = True
End Sub


Be cautious with this. If there is an error in your code and execution halts, Events will not be reenabled and some functionality may "disappear". You need to add proper error handling to reenable events in the event of code failure.

JG4life
03-31-2006, 10:52 AM
Too Funny, that was the exact code I was talking about supressing errors, just didn't think it worked for subroutines. Useful little tidbit of information.

Thanks again, you guys rock!!!

Sean

mdmackillop
03-31-2006, 10:57 AM
One other line of code you might find very useful in sheet event macros, usually placed at the start of your code, it avoids error messages etc. when multiple cells are selected for deletion etc.

If Target.Cells.Count>1 then Exit Sub

xld
03-31-2006, 10:57 AM
Too Funny, that was the exact code I was talking about supressing errors, just didn't think it worked for subroutines. Useful little tidbit of information.

Thanks again, you guys rock!!!

Sean

It doesn't suppress procedures, it just turns off event enabling, so events do not work any longer. Any other procedures will work as normal.