ComboBox_Click refresh upon opening worbook, and refresh external data import and throws error

[Stphn]

Hi

In my spreadsheet, I import data from an external database. This dataset provides me with a list of names, which I called "ListStocks". this list is the input to my ComboBox.
The idea of the ComboBox is that when people change the selected name, 3 little macros run, updating a report for the selected name. These macros are related to 3 charts (i.e. changing the scale of the axis in 2 and changing the datasource in the other).

Problem, when I open the spreadsheet, the external query pop-up asks whether I want to enable or disable the automatic refresh, where I click "Enable" to update the underlying dataset.
And then, an error box pops up "Run-time error '1004': Unable to get the ChartsObject property of the Worksheet class". And on Debug, the second line of the first chart macro is highlighted:

"ActiveSheet.ChartObjects("Stock iVol").Activate"

The first line was "Sheets("Stock detail").Select"


Same error with ComboBox_Change - which I can understand because while the reference list of the combobox refershes, the value will change and the macro is triggered.
But with ComboBox_Click, I don't click the box when enabling the automatic refresh?

This error doesn't pop-up with ComboBox_DoubleClick, but the double_click doesn't behave as I want it later on. User will only ever click on the downarrow and select anotjer name from the list and will expect the report to update?

Any ideas how to avoid triggering ComboBox_Click upon opening workbook until the database connection is closed again?

(If at least you agree that that is the problem?)

Thanks a lot!!

 

[Stphn]

Hi

Me again.
I tried opening the spreadsheet with ComboBox_ClickonArrow and that doesn't throw the error, but that also isn't the behaviour I want in the sheet. The macros should only be called after the selection has changed.

Still looking for a way to disable the combobox while the connection with the external database is open.
Possibly:
Sub Workbook_open
1) disbale combobox
2) enable automatic refresh
3) enable combobox when dataconnection is closed
End sub

And ideally keep ComboBox_Click.

Any thought? Any pointers at all?

Thanks so much!