Copy/Paste a checkbox in Excel
I have created a form using checkboxes from the forms toolbox. I want to sum all boxes checked. I understand I have to link each box to a cell in order to set the value to True or False. I have several hundred boxes on the form. How do I copy/paste a box with its linked cell reference (as I do with other formulas)A1 = A1, A2 = A2 and so on. Instead,when I copy A1 to A2, A2 has the same referenced cell (A1). I have to go to each cells properties and set the cell. I'm sure there is a solution without me having to touch each box and set its properties.
Thanks in advance
Thanks in advance
ASKER
Hi dbase118
I guess your telling me that there is no solution in Excel. I thought about trying Access for this backup sheet but have not tried doing so because I don't know how to or if I can tie Access with Excel. I have basically two sheets in my workbook, one is a macro level of work performed, the other gets down to the nitty-gritty. Basically A1 = record #, B1-D1 = what action was required to complete using a checkbox. I wanted to total counts based on a check (true) and post it on the front sheet as a summary of actions performed.
So yes give me details on the Access option. Can I use excel and access together. I know there is a pivot-table option but have never tried using it as a database.
Thanks for your help
I guess your telling me that there is no solution in Excel. I thought about trying Access for this backup sheet but have not tried doing so because I don't know how to or if I can tie Access with Excel. I have basically two sheets in my workbook, one is a macro level of work performed, the other gets down to the nitty-gritty. Basically A1 = record #, B1-D1 = what action was required to complete using a checkbox. I wanted to total counts based on a check (true) and post it on the front sheet as a summary of actions performed.
So yes give me details on the Access option. Can I use excel and access together. I know there is a pivot-table option but have never tried using it as a database.
Thanks for your help
Not saying its not possible in Excel, I just dont know of a quick and easy way. Let me get my steps together and I will post them.
Hi,
Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then s = s + 1
If CheckBox2.Value = True Then s = s + 1
If CheckBox3.Value = True Then s = s + 1
MsgBox s
End Sub
Enjoy
Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then s = s + 1
If CheckBox2.Value = True Then s = s + 1
If CheckBox3.Value = True Then s = s + 1
MsgBox s
End Sub
Enjoy
If we're talking about a user form, a better way to get the sum of checked boxes would probably be a loop to look at all your controls. In the sample below, I'm using control name and assuming all your checkboxes start with "checkbox" (any similar convention would work).
Private function countChecked()
countChecked = 0
For Each ctlObj in Me.Controls
If Left$(ctlObj.Name, 8) = "Checkbox" Then
If ctlObj.Value Then
countChecked = countChecked + 1
End If
End If
Next ctlObj
End Function
Private function countChecked()
countChecked = 0
For Each ctlObj in Me.Controls
If Left$(ctlObj.Name, 8) = "Checkbox" Then
If ctlObj.Value Then
countChecked = countChecked + 1
End If
End If
Next ctlObj
End Function
Addendum: seems to me there is a property (class??) that tells you what kind of control you're looking at, but I don't see it in the list and would have to research. This would obviously be more reliable than name, if it exists for VBA controls.
ASKER
Sorry, but I am not sure how to set up the function. I placed it in a sub but got an error.
Formula =countif(C3:C500,"True") works only when the checkbox's format control cell link has $C$3. This results in a count of one. I can't seem to copy paste this in other cells (it still links to $C$3 unless I individually go to each format control cell link and select $C$4 and so on). How can I automate this action. I have some 500 checkboxes.
Formula =countif(C3:C500,"True") works only when the checkbox's format control cell link has $C$3. This results in a count of one. I can't seem to copy paste this in other cells (it still links to $C$3 unless I individually go to each format control cell link and select $C$4 and so on). How can I automate this action. I have some 500 checkboxes.
ASKER
Sorry, but I am not sure how to set up the function. I placed it in a sub but got an error.
Formula =countif(C3:C500,"True") works only when the checkbox's format control cell link has $C$3. This results in a count of one. I can't seem to copy paste this in other cells (it still links to $C$3 unless I individually go to each format control cell link and select $C$4 and so on). How can I automate this action. I have some 500 checkboxes.
Formula =countif(C3:C500,"True") works only when the checkbox's format control cell link has $C$3. This results in a count of one. I can't seem to copy paste this in other cells (it still links to $C$3 unless I individually go to each format control cell link and select $C$4 and so on). How can I automate this action. I have some 500 checkboxes.
When you said you set up a form, I assumed you meant you set up a user form in VB Editor. If this is the case, you would put the function in the user form's code and call it whenever you want to know how many are checked.
If by "form" you mean a worksheet, the approach is slightly different. You would need to insert a module in the workbook using VB Editor and create a custom worksheet function as follows.
Public Function countChecked()
countChecked = 0
For Each ctlObj in Application.Caller.Parent.
If Left$(ctlObj.Name, 9) = "Check Box" Then
If ctlObj.Value = 1 Then
countChecked = countChecked + 1
End If
End If
Next ctlObj
End Function
You would then put the formula =countChecked() somewhere in the worksheet containing the checkboxes. This will return number of checked boxes on just that sheet.
If by "form" you mean a worksheet, the approach is slightly different. You would need to insert a module in the workbook using VB Editor and create a custom worksheet function as follows.
Public Function countChecked()
countChecked = 0
For Each ctlObj in Application.Caller.Parent.
If Left$(ctlObj.Name, 9) = "Check Box" Then
If ctlObj.Value = 1 Then
countChecked = countChecked + 1
End If
End If
Next ctlObj
End Function
You would then put the formula =countChecked() somewhere in the worksheet containing the checkboxes. This will return number of checked boxes on just that sheet.
Oops, forgot you need to put Application,Volatile as 1st line of worksheet function. And it still doesn't autocalc, you have to push F9. Nothing's perfect...
ASKER
Hi jeffmeyn
Pretty cooool !
I tried the function with and without Application.Volitile and didn't see any difference. When I press F9 nothing happens. If I copy the cell containing =countChecked() and paste again it will recalculate. Am I doing something wrong here.
Okay, now that the worksheet is counting, can I now define it to calculate each column separately.
Thanks
Pretty cooool !
I tried the function with and without Application.Volitile and didn't see any difference. When I press F9 nothing happens. If I copy the cell containing =countChecked() and paste again it will recalculate. Am I doing something wrong here.
Okay, now that the worksheet is counting, can I now define it to calculate each column separately.
Thanks
ASKER
Hi jeffmeyn
Pretty cooool !
I tried the function with and without Application.Volitile and didn't see any difference. When I press F9 nothing happens. If I copy the cell containing =countChecked() and paste again it will recalculate. Am I doing something wrong here.
Okay, now that the worksheet is counting, can I now define it to calculate each column separately.
Thanks
Pretty cooool !
I tried the function with and without Application.Volitile and didn't see any difference. When I press F9 nothing happens. If I copy the cell containing =countChecked() and paste again it will recalculate. Am I doing something wrong here.
Okay, now that the worksheet is counting, can I now define it to calculate each column separately.
Thanks
Right, I saw the same thing. Any instances of the function you put into a worksheet BEFORE you added Application.Volatile will not recalc, any instances you put in AFTER will recalc. It's like the function remembers it isn't volatile. Unfortunately, since Excel's calc engine only looks at cell values, I don't think there's any way to get this thing to autocalc.
ASKER
Since it is calculating all boxes, the process takes awhile. I know its overkill, but my sheet has 4 cols with 500 boxes. I would prefer to calc each column individually so I can categorize them on another sheet. By the way, F9 is now working...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The sheet is protected. I will have to try another way. Maybe in access.
Thanks for your excellent help
Thanks for your excellent help
If it takes a long time to calc, do you have some other heavy-duty formulas? Because I just created a sheet with 500 checkboxes, and it takes less than a second to calc.
Anyway...to limit search to a single column, allow for an argument (which should be the column in the worksheet you want to check). Using the With structure may, possibly, speed things a little.
Public Function countChecked(colRng)
Application.Volatile
countChecked = 0
For Each ctlObj in Application.Caller.Parent.
With ctlObj
If Left$(.Name, 9) = "Check Box" Then
If .Column = colRng.Column Then
If ctlObj.Value = 1 Then
countChecked = countChecked + 1
End If
End If
End If
End With
Next ctlObj
End Function
Anyway...to limit search to a single column, allow for an argument (which should be the column in the worksheet you want to check). Using the With structure may, possibly, speed things a little.
Public Function countChecked(colRng)
Application.Volatile
countChecked = 0
For Each ctlObj in Application.Caller.Parent.
With ctlObj
If Left$(.Name, 9) = "Check Box" Then
If .Column = colRng.Column Then
If ctlObj.Value = 1 Then
countChecked = countChecked + 1
End If
End If
End If
End With
Next ctlObj
End Function
ASKER
Thanks jeffmeyn
I will try this tonight...
I will try this tonight...
ASKER
jeffmeyn,
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
ASKER
jeffmeyn,
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
ASKER
jeffmeyn,
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
If .Column = colRng.Column Then
Not sure about setting up the range. If .Column = ????
Can you help
I noticed your checkbox question yesterday. Would importing your table into access be an option. I only ask because this is like a 2 minute job in Access. If so, let me know and i will post details.