1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Referencing CheckBoxes in an Excel WorkSheet

Discussion in 'Business Applications' started by andy7472007, Jan 5, 2013.

Thread Status:
Not open for further replies.
  1. andy7472007

    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
     
  2. CDHarm

    CDHarm

    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
    
     
  3. andy7472007

    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.
     
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 733,556 other people just like you!

Loading...
Similar Threads - Referencing CheckBoxes Excel
  1. estyMonsty
    Replies:
    7
    Views:
    470
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083812

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice