help with check box in excel

[forkus2000]

Hi guys, i have a visual basic program, and i need to read a excel worksheet, in this sheet are listboxes, checkboxes, and groupboxes, to access the listboxes i use
value =objExcelWks.Shapes("Lista").OLEFormat.object.Value

but the checkboxes and group boxes don`t have a value property.

Please help me

Regards Paolo

[mikerickson]

If the controls are from the Forms menu, the syntax is
ActiveSheet.Shapes("Check Box 1").ControlFormat.Value
with will return either -4146(unchecked) or 1(checked).
CBool(ActiveSheet.Shapes("Check Box 1").ControlFormat.Value And 1)returns True or False.

[Bob Phillips]

It its a control toolbox try



value = objExcelWks.OLEObjects("CheckBox1").Object.value

[forkus2000]

Hi guys thanks, your tip work fine, but i have a big trouble.

I have 1000 excel files , i suppose are identical, each file have a poll , i make a visual basic program to open the file , get the information , save the data in a database, close the excel file, open other file and continue to the 1000 excel files.
But some files the visual basic origine an error 80070057 "No se encontro el elemento especificado" in english "The specific element was not found"
I open the bad excel file and i check that the name of the element exist whit the correct name, but if make click in the form object and then save the file and start the program again that works fine.

When i make click in some forms controls and save works poerfectly, how can i automate this in visual basic for repair the bad files ?


Please respond Asap

[mikerickson]

If there is only one object per sheet, you can change the routine to use numerical indexes rather than strings.

Otherwise, you probably need human judgement to determine which of the controls has a mis-spelt name. This routine might help you find the bad names

Dim wb in Workbook

For each wb in Application.Workbooks
On Error Resume Next
If wb.Sheets("theSheet").Shapes("TrueName").Name <> "TrueName" Then
MsgBox wb.Name & " has a bad name."
End If
On Error Goto 0
Next wb