Referencing Checkbox Values in VBA

**IndyTF** · Dec 17th, 2008, 07:21 PM

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!

**Blade Hunter** · Dec 17th, 2008, 07:33 PM

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?

**Boller** · Dec 17th, 2008, 07:51 PM

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

(Note : It can work without .Value because Value is the default Property of a CheckBox)

**IndyTF** · Dec 17th, 2008, 08:05 PM

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?

**IndyTF** · Dec 17th, 2008, 08:06 PM

Boller's suggestion worked. Thank you!

Not sure what I was doing wrong.

**IndyTF** · Feb 3rd, 2009, 04:28 PM

*** 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.