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?
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!
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.
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.
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?
Boller's suggestion worked. Thank you!
Not sure what I was doing wrong.
*** 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
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.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.
Example:
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.Code:If Sheets("Analytics").CheckBoxes("chbx_111") = True Then Range("D19").Value = Range("C19").Value Else Range("D19").Value = 0 End If
An example of what I am looking for is shown:
Then....Code:Public Function CheckBoxChecked(CheckBoxName As CheckBox) As Boolean If CheckBoxName = 1 Then CheckBoxChecked = True Else CheckBoxChecked = False End If End Function
=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.
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