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

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
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:

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

I've tried variations on this such as:

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


Please can anyne point me in the right direction? Thanks
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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

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

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
No, think I tried that first, but I still get an error:

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
What happens with the following code?

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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi Neil,

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,279
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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Thanks Tom, That works super. Thanks for your help too Neil
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top