I'm writing this Macro that automatically generates a report by getting data from 10 Excel Spread Sheets(Workbook A lets say) and putting them in one spread sheet(the report,Workbook B). The 10 spread sheets are in different folders. The spread sheets have check boxes in them and I have to get the state of the check box (true/false) for each spread sheet. The problem I'm having is I cannot get the state of the check box from Workbook B. The way I have the Macro set up, the macro opens each excel file and gets the data from the correct cells and writes it to the report, this part works. I'm trying to do the same thing with the check box. I need to know if the box is checked, if its checked then a variable is incremented and this variable is written to the spread sheet. If I run the following command in Workbook A, I can get the value of the check box.

Sub Test()

Dim temp as boolean

temp = CheckBox10.Value

End Sub


(^This code works, but it is in Workbook A)

I need to do the same thing in Workbook B but I am unsure of what code I should use. Here is what I have tried so far.

Sub Test()

Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim myCheckBox As OLEObject
Dim temp As String

Set xlBook = GetObject("C:\Workbook1.xls")

Set xlSheet = xlBook.Sheets("Worksheet1")

Set myCheckBox = xlSheet.OLEObjects("CheckBox10")

temp = myCheckBox.Application.Value

End Sub

(This code does not give the desired result and is in Workbook B )

This code does not return an error, but temp = "Microsoft Excel". It should be a boolean but I found that if I make it a boolean I get an error. Anyone know how I can get this to work? I tried for like 2 hours + yesterday and I don't want to waste another day guessing.

I didn't get past the 4th word. Sorry.

i'm no vb expert but why did you use in your example:

temp = CheckBox10.Value

and in example 2 it changed to:

temp = myCheckBox.Application.Value

what's the significance of .Application?

The reason I used .application is because myCheckBox.value does not exist but for some reason myCheckBox.Application.Value does exist. I'm not a VB expert either, so when it doesn't work I start to guess.

have you tried:

temp = xlSheet("CheckBox10").Value

?

Thanks for the tip. That command doesn't work, but a very similar one does.

temp = xlbook("SheetA").CheckBox10.Value

I should have posted this yesterday

cool, sounds like you found an answer

Yup, I just finished coding it and it looks like everything works. One of the guys from my group said in 2 weeks they are pushing out a change to the format of report so I will have to go back in and change some of the code