Results 1 to 7 of 7

Referencing Checkbox Values in VBA

This is a discussion on Referencing Checkbox Values in VBA within the Excel Questions forums, part of the Question Forums category; Hello all, I tried to search for my answer already, and while there are quite a few posts, I had ...

  1. #1
    New Member
    Join Date
    Oct 2008
    Location
    Utah
    Posts
    19

    Default Referencing Checkbox Values in VBA

    Hello all,

    I tried to search for my answer already, and while there are quite a few posts, I had a hard time finding an answer. So here goes:

    I have inserted some checkboxes into a worksheet using the Forms Toolbar. Since I am somewhat crazy about having things named so they can be easily referenced, I simply selected each (total of 8) of the checkboxes and renamed them using the Name Box (to the left of the formula viewer) in Excel. So, for example, I have now:

    chbx_000
    chbx_001
    chbx_010
    chbx_100
    chbx_111
    etc....

    Let's say that these checkboxes are on a worksheet called: "Analytics". How do I reference the value of the checkbox (yes/no) in VBA? I don't want to link the checkbox to a cell if I don't have to.

    I thought it would be just something like this:

    sheets("Analytics").chbx_000.value

    That didn't work. Any suggestions? Thank you in advance!

  2. #2
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    2,945

    Default Re: Referencing Checkbox Values in VBA

    That is correct:

    sheets("Analytics").chbx_000.value (Or without .value will work as it is a boolean response).

    You renamed the (Name) in the properties and not the caption right?
    .select, overused and inefficient.

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Posts
    2,141

    Default Re: Referencing Checkbox Values in VBA

    Sheets("Analytics").CheckBoxes("chbx_000")

    (Note : It can work without .Value because Value is the default Property of a CheckBox)
    Last edited by Boller; Dec 17th, 2008 at 08:55 PM.

  4. #4
    New Member
    Join Date
    Oct 2008
    Location
    Utah
    Posts
    19

    Default Re: Referencing Checkbox Values in VBA

    Bladehunter:

    Not sure what you mean. I basically just used the Name Box in Excel to change the name. You know - the way you name an Excel Range.

    BTW: Sorry I forgot to say this last time. I am using Excel 2002/2003.

    Is that wrong? When I right-click on the checkbox, I have the options of assigning a macro or "format Control". Clicking on format control opens a window with a couple of tabs, but none offer a name or caption? the properties tab only lets me check to print object or move the control.

    Ideas?

  5. #5
    New Member
    Join Date
    Oct 2008
    Location
    Utah
    Posts
    19

    Default Re: Referencing Checkbox Values in VBA

    Boller's suggestion worked. Thank you!

    Not sure what I was doing wrong.

  6. #6
    New Member
    Join Date
    Oct 2008
    Location
    Utah
    Posts
    19

    Default Re: Referencing Checkbox Values in VBA

    *** NEW QUESTION ***

    Since it is along the same lines, I thought that I would ask this question here instead of posting a new thread.

    So here is the deal: How can I reference a Checkbox in a VBA Function and let the worksheet know that I am doing this so it recalculates anything relying on that Checkbox? I know that the question is somewhat confusing, so here is what I mean:

    Taken from another forum http://www.eggheadcafe.com/software/...nt-update.aspx

    Excel will only calculate a cell when it needs to be calculated; that is, when a precedent of the cell containing the function is changed. If your function reads values from a cell directly, Excel may not recalculate the function when that cell changes because Excel doesn't and can't know what cells are being used within VBA code.

    An example will illustrate this clearly. Consider the following VBA function.

    Function MyAAA() As Long
    MyAAA = Range("A1").Value * 10
    End Function

    With this function, which refers directly to cell A1, Excel will not recalculate the function when A1 is changed, because Excel can't know that the VBA code refers to A1. A much better function would be

    Function MyAAA(TheCell As Range) As Long
    MyAAA = TheCell.Value * 10
    End Function

    With this second function, the cell reference is passed to the function,
    with a call like

    =MyAAA(A1)

    Since cell A1 is referenced in the formula, not the function, Excel will properly recalculate the function.
    So my question is this: How can I reference the checkbox inside a Function (which is a personally written function in VBA), so that I can then use the function nested in other functions elsewhere in the worksheet? I know that I can set values in VBA when a checkbox is clicked by linking that control checkbox to a pre-set macro.

    Example:
    Code:
    If Sheets("Analytics").CheckBoxes("chbx_111") = True Then
        Range("D19").Value = Range("C19").Value
        Else
        Range("D19").Value = 0
    End If
    The problem with this is that I must already know all the areas I will use it and I can't nest the value inside other functions.

    An example of what I am looking for is shown:

    Code:
     
    Public Function CheckBoxChecked(CheckBoxName As CheckBox) As Boolean
    If CheckBoxName = 1 Then
        CheckBoxChecked = True
        Else
        CheckBoxChecked = False
    End If
    End Function
    Then....
    =IF(Checkboxchecked("chbx_000"),"Yes","False")

    Then I could use this inside a function elsewhere, right? Obviously it doesn't work right now and that is why I am asking it here. Is this possible? How do you use the "As Checkbox" definition inside the inputs for a Function?

    Thanks in advance for your help and ideas.

  7. #7
    New Member
    Join Date
    Oct 2008
    Location
    Utah
    Posts
    19

    Default Re: Referencing Checkbox Values in VBA

    Bumping. I am very curious about if this is possible. See above post.

    Thanks! Sorry for bumping, but this board is so busy.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com