CODEWITHSUNDEEP
excelvbacombobox
user3469225
user3469225

Reputation: 1

Multiple Worksheet_change events firing each other

I have a workbook with multiple Worksheet_Change events in the sheet. Each of these macroes are related to an active x combobox.

The problem is that when I change one of the comboboxes the macro fires (as expected), which in turn start another macro (which is not suppose to run). I have already set the Application.EnableEvents = False But the issue might be that I am changing a cell.value, which is linked to another combobox and hence also linked to another worksheet_change event.

A workaround as I see it, might be to only run the macro, if the combobox is the one actually selected, but here comes the second problem. I can't find a way to have vba return the name of the active combobox.

Please note that these comboboxes is not connected to a userform, they are simply placed directly on the worksheet.

Is there anybody who has any idea on how to solve this??

Any help is much appreciated,

Upvotes: 0

Views: 1515

Answers (4)

user3469225
user3469225

Reputation: 1

Thanks a lot for the quick responses.

I ended up using a named cell in the worksheet, similar to what hstay sugested.

If ThisWorkbook.Sheets("MD").Range("AllowMacro").Value = 0 Or Me.Name <> ActiveSheet.Name Then Exit Sub

However as I need the worksheet to calculate some cells while the code is running, I can't set application.Calculation = xlManual. So the code still tries to execute a bunch of other change events, but the code above stops them from running more than just the first line. This however still takes a lot of time, which is quite frustrating. Guess I'll just need to take this in to account another time.

This is how I begin and end all my worksheet_chnage events:

If ThisWorkbook.Sheets("MD").Range("AllowMacro").Value = 0 Or Me.Name <> 
ActiveSheet.Name Then Exit Sub
ThisWorkbook.Sheets("MD").Range("AllowMacro").Value = 0
Application.ScreenUpdating = False
Application.EnableEvents = False

'some random code that trigger other change_events

Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Sheets("MD").Range("AllowMacro").Value = 1

Upvotes: 0

hstay
hstay

Reputation: 1439

You could also set a global variable handling events. You check it at the beginning of each change event. 

Dim ufEventsDisabled As Boolean

Sub YourSub()
    ufEventsDisabled = False
    Range("A1").Value=1 'This triggers the event

    ufEventsDisabled = True
    Range("A1").Value=1 'This doesn't trigger the event

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If ufEventsDisabled=True Then Goto ExitEvent:
    'Your regular worksheet code

ExitEvent:
    ufEventsDisabled=False
End Sub

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

I see that there are 2 possible solutions...

1 - as stated by Gary's Student, you may have toggled the Application.EnableEvents somewhere unknowingly and you could try and trap where that happens.

2 - Maybe set a global boolean variable called, say DontRunMacros and set it to TRUE at the start of the first macro and to FALSE at the end. Then you simply have each other macro start with If DontRunMacros Then Exit Sub - That will prevent the others running regardless of the events that fire...

Hope that helps!

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

There is probably an error somewhere that is re-Enabling Events...........You can always insert:

MsgBox Application.EnableEvents

at points in your code to trap this.

Upvotes: 0

Related Questions