VBA Checkbox problem

Hi! my first post and my first question:

Ive got an excel file that has got 6 checkboxes placed on the worksheet ( sheet 1 ), not on a user form ( I think they are not ActiveX controls, they come from the FORM tab ). The sheet is protected and I can't right click on the checkboxes to see where they are linked. I don't know the name given to the checkboxes ( If any name has been given to them, I don't actually even know if you can rename a checkbox control ).

Furthermore, the protected excel file has no VBA code in it ( afaik )

Thing is, I'm doing a module that reads some values of some specified cells in the protected sheet and writes them on another workbook. I need to check wich checkboxes are marked as TRUE, or ticked, and I don't have the slightest idea of how to do it.

I need to find a way to retrieve the value of those checkboxes.


Thanks in advance and sorry for my faulty English ^^

Hi Larrynho and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

You can get the values from the checkboxes by looping through the worksheet object's shapes collection. I'll give you a little to get you started, but I'll leave you to work out how to retrieve the value.... ;)


Sub GetValues()
Dim wstSheet1 As Worksheet
Dim shp As Shape

Set wstSheet1 = Worksheets("Sheet1")

For Each shp In wstSheet1.Shapes
If TypeName(shp.OLEFormat.Object) = "CheckBox" Then

'get the name / value / text etc....

End If
Next shp
End Sub


Look up any words you're not familiar with in the Excel VBA helpfile.
Colin

Thank you very much Colin!

I'll give your solution a try, I'll post later the results. And thanks for guiding me to the posting guidelines too !

sorry for my faulty English
Just to reassure you - your English is just fine. We get much worse from native speakers! ;)

Yay, worked !

In the end "my" code goes as follows:
________________________________________________________
For Each shp In oinforme.ActiveSheet.Shapes
If TypeName(shp.OLEFormat.Object) = "CheckBox" Then
MsgBox shp.ControlFormat
End If
Next shp
_________________________________________________________
Getting 1 as value in the msgbox if the chkbx is marked and where oinforme is the Excel.WorkBook from where I'm retrieving data.

Thanks again!

PD: Cas, :) , I try to shape up my posts a bit before posting them, so no one get eye-hache hahaha