Dr.K
11-01-2007, 11:29 AM
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.
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
Is there a better way to do this?
Why are these events being triggered if the worksheet that contains the code is not active?
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.
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
Is there a better way to do this?
Why are these events being triggered if the worksheet that contains the code is not active?