Private ComboBox Event Subs NOT staying private?

[Dr.K]

I am in charge of updating an internal data "compiler" workbook every month with new data. I've modified the code a bit here and there, but I did not create the workbook.

Basically, its a workbook with a ton of data on different worksheets.
The main worksheet has several ComboBoxes which allow you pull up specific data sets, and then cells fill with data based on Vlookups for the data sheets.

Two ComboBoxes are tied to each other: one displays a Ticker symbol for a mutual fund, and the other the Lipper Short Name for the fund. You are able to enter into EITHER ComboBox, with the other one updating automatically. I tied them together with Private Subs in the worksheet module, fired by the "ComboBox_Change" event.

However, if you had multiple workbooks open, the code tries to run even when you are in different workbooks!! This causes immediate errors, since the referenced objects are not availible (since the wrong workbook and is active!)

As a work around, I put in a set of IF statements to dump out if the correct workbook and worksheet are not active.

[vba]Private Sub CBox_Ticker_Change()

If Not ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
If Not ActiveSheet.Name = "Calculate" Then Exit Sub
If CBox_Ticker.MatchFound = False Then Exit Sub

Application.EnableEvents = False
CBox_Name.ListIndex = CBox_Ticker.ListIndex
Range("A6").Value = CBox_Name.Value
Application.EnableEvents = True

End Sub


Private Sub CBox_Name_Change()

If Not ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
If Not ActiveSheet.Name = "Calculate" Then Exit Sub
If CBox_Name.MatchFound = False Then Exit Sub

Application.EnableEvents = False
CBox_Ticker.ListIndex = CBox_Name.ListIndex
Range("A6").Value = CBox_Name.Value
Application.EnableEvents = True

End Sub[/vba]


Is there a better way to do this?
Why are these events being triggered if the worksheet that contains the code is not active?

[figment]

i am not sure why those events would be triggering from diffrent sheets, unless you have mupltipl sheets with combo boxes all called the same thing. but if you looking to reduce lines of code, you can reduce all your if statments down to one with this

[VBA]Private Sub CBox_Ticker_Change()

If ActiveWorkbook.Name = ThisWorkbook.Name And ActiveSheet.Name = "Calculate" _
And CBox_Ticker.MatchFound = True Then
Application.EnableEvents = False
CBox_Name.ListIndex = CBox_Ticker.ListIndex
Range("A6").Value = CBox_Name.Value
Application.EnableEvents = True
End If
End Sub

Private Sub CBox_Name_Change()

If ActiveWorkbook.Name = ThisWorkbook.Name And ActiveSheet.Name = "Calculate" _
And CBox_Name.MatchFound = True Then
Application.EnableEvents = False
CBox_Ticker.ListIndex = CBox_Name.ListIndex
Range("A6").Value = CBox_Name.Value
Application.EnableEvents = True
End If
End Sub[/VBA]