Referencing Checkbox Values in VBA

IndyTF

New Member
Joined
Oct 23, 2008
Messages
19
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
Sheets("Analytics").CheckBoxes("chbx_000")

(Note : It can work without .Value because Value is the default Property of a CheckBox)
 
Last edited:
Upvote 0
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?
 
Upvote 0
*** 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/aspnet/31443314/vba-function-wont-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.
 
Upvote 0
Bumping. I am very curious about if this is possible. See above post.

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

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top