Hi there all,
I have a macro that allows the user to search pick and load other workbooks held on the network. Once the other workbook is loaded, you get a front sheet showing a dashboard of figures and two combobox - one showing month and then one showing year so that the user can display different data.
My problem is that when that other workbook is loaded, both combobox are empty. They do populate as the comboboxes are filled when the worksheet is activated (worksheet activate sub) but of course this only happens if you manually select another sheet and then select to said sheet.
I have tried two options and both have failed. The first was to reference the comboboxes and populate them from the macro running workbook - so populating a combobox on a worksheet in another workbook. The other method involves code within the workbook that actually has the comboboxes, but because this is a template people are submitting, I can't change that quickly or I'll have to make x amount of changes for each of the workbooks. Better to have the macro running workbook send something to the comboboxes in the opened workbook. But how do I do this? At the moment the following brings back an error - 'Unable to get object property of OLEObject class'
This seemed to work in Excel 2010. I ran into probs with 2007. I also tried using OLEObject too, something like:
activesheet.OLEObjects("Combobox1").object.additem "*"
But that brought up the same error. Can anyone please help me?
I have a macro that allows the user to search pick and load other workbooks held on the network. Once the other workbook is loaded, you get a front sheet showing a dashboard of figures and two combobox - one showing month and then one showing year so that the user can display different data.
My problem is that when that other workbook is loaded, both combobox are empty. They do populate as the comboboxes are filled when the worksheet is activated (worksheet activate sub) but of course this only happens if you manually select another sheet and then select to said sheet.
I have tried two options and both have failed. The first was to reference the comboboxes and populate them from the macro running workbook - so populating a combobox on a worksheet in another workbook. The other method involves code within the workbook that actually has the comboboxes, but because this is a template people are submitting, I can't change that quickly or I'll have to make x amount of changes for each of the workbooks. Better to have the macro running workbook send something to the comboboxes in the opened workbook. But how do I do this? At the moment the following brings back an error - 'Unable to get object property of OLEObject class'
Code:
Set owb = Workbooks.Open(strRootFolder & Me.ListBox1 & "\" & Me.ListBox2, , True, , , , True, , , , , , False)
kpiws.Visible = True
kpiws.Activate
With ActiveSheet.Combobox1
.Clear
.AddItem "January"
.AddItem "February"
.AddItem "March"
End with
This seemed to work in Excel 2010. I ran into probs with 2007. I also tried using OLEObject too, something like:
activesheet.OLEObjects("Combobox1").object.additem "*"
But that brought up the same error. Can anyone please help me?