Referencing CheckBoxes in an Excel WorkSheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

andy7472007

Thread Starter
Joined
Jan 4, 2013
Messages
2
Hi,

I am working on an Excel worksheet with over 200 checkboxes (CheckBox1, CheckBox2, etc.). I use VB6 to open the Excel worksheet but accessing the value of the cell where the checkbox is, is very challenging. I have used the following in VB6 code to no avail:

'the cell contains a checkbox, and i want to see the logical value (checked or unchecked) so i can assign a 'corresponding numerical value but the content it's showing is 'Empty'. It's a protected worksheet btw.

xlsWB1.WorkSheets(strWorkSheetName).Select
xlsWB1.WorkSheets(strWorkSheetName).Range("H8").Value 'this shows me an 'Empty' value

Also tried this one but gave me this runtime error: 'Unable to get the CheckBoxes Property of the WorkSheet class'

xlsWB1.WorkSheets(strWorkSheetName).Select
xlsWB1.Activesheet.CheckBoxes("CheckBox5").Value

Also this without success:

xlsWB1.WorkSheets(strWorkSheetName).Select
xlsWB1.Activesheet.CheckBox5.Value 'Error says: Object doesn't support this property or method

I am not sure how to proceed with this and suggestions would be most welcome.

-Andy7472007
 
Joined
May 26, 2011
Messages
255
Hi,

I do not think the check boxes are linked to the cells.
The follow code will loop thru the
"Forms" checkbox and the "ActiveX" checkboes.
You will not that they use a integer. If you have 200 of them then change the sample to 200.

Code:
Sub test()
'' For ActiveX ''
 Dim i As Integer
    For i = 1 To 4
    With ActiveSheet
        If .OLEObjects("CheckBox" & i).Object.Value = True Then
        MsgBox ""
        End If
    End With
    Next i
End Sub
Code:
Sub test1()
'' For "Forms checkboxes"'''
    For i = 1 To 36
        With ActiveSheet
            If .CheckBoxes("Check Box " & i).Value = 1 Then
            ''Do something ''
            End If
        End With
    Next
End Sub
 

andy7472007

Thread Starter
Joined
Jan 4, 2013
Messages
2
Hi,

You are right. It's not linked to the cell where the checkbox is. I will test your code and see what happens.

Many thanks for the suggestions.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top