Bah, what am I doing wrong with this line of code?*resolved*

[thorpyuk]

Hiya,

I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my code:

If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"

I've tried variations on this such as:

If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"

Please can anyne point me in the right direction? Thanks

 

[njimack]

Just a thought - if 'Temp' is the active workbook, I don't think 'CRM 2' will have an active sheet (I could be wrong). Have you tried using an actual sheet name, such as

If Windows("CRM 2.xls").Sheets("Sheet1).CheckBox6.Value = True Then Selection.Value = "By Phone"

 

[njimack]

Just a thought - if 'Temp' is the active workbook, I don't think 'CRM 2' will have an active sheet (I could be wrong). Have you tried using an actual sheet name, such as

If Windows("CRM 2.xls").Sheets("Sheet1").CheckBox6.Value = True Then Selection.Value = "By Phone"

 

[thorpyuk]

No, think I tried that first, but I still get an error:

If Windows("CRM 2.xls").Sheets("Complaint").CheckBox6.Value = True Then Selection.Value = "By Phone"

 

[njimack]

What happens with the following code?

Msgbox Windows("CRM 2.xls").Sheets("Complaint").CheckBox6.Value

If for some reason the checkbox value isn't being retrieved thru VBA, you may need to reference a cell that's linked to the checkbox.

 

[thorpyuk]

Hi Neil,

No, I get the same error - 'object does not support this property or method'

 

[njimack]

Ah, you never mentioned the error message before.

Does the error still occur if you make CRM 2 the active workbook?

Did you consider my suggestion of referencing a cell that's linked to the checkbox?

 

[Tom Urtis]

Don't use Windows as the parent object, use Workbooks instead.

This works:

If Workbooks("CRM 2.xls").Worksheets("Complaint").CheckBox6.value = True Then Selection.value = "By Phone"

Assumes both workbooks are open in the same instance of Excel.

 

[thorpyuk]

Thanks Tom, That works super. Thanks for your help too Neil