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
asasakAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jeffmeynConnect With a Mentor Commented:
If the checkboxes are named in a way that you can easily identify what column they belong to, you could use that as a criterion.  Alternatively, you could check the TopLeftCell property of each checkbox to see what column it's in - but if anything that would slow things down more.  Unfortunately, the DrawingObjects collection belongs to the sheet, not the range.  You could check VB Help to see if SpecialCells has a constant for drawing objects (provided your sheet is not protected).
0
 
dbase118Commented:
asasak,

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.
0
 
asasakAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dbase118Commented:
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.
0
 
TrueDrakeCommented:
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
0
 
jeffmeynCommented:
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
0
 
jeffmeynCommented:
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.
0
 
asasakAuthor Commented:
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.  
0
 
asasakAuthor Commented:
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.  
0
 
jeffmeynCommented:
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.DrawingObjects
       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.
0
 
jeffmeynCommented:
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...
0
 
asasakAuthor Commented:
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
0
 
asasakAuthor Commented:
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
0
 
jeffmeynCommented:
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.
0
 
asasakAuthor Commented:
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...
0
 
asasakAuthor Commented:
The sheet is protected.  I will have to try another way.  Maybe in access.

Thanks for your excellent help
0
 
jeffmeynCommented:
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.DrawingObjects
        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
0
 
asasakAuthor Commented:
Thanks jeffmeyn

I will try this tonight...
0
 
asasakAuthor Commented:
jeffmeyn,

If .Column = colRng.Column Then

Not sure about setting up the range.  If .Column = ????

Can you help
0
 
asasakAuthor Commented:
jeffmeyn,

If .Column = colRng.Column Then

Not sure about setting up the range.  If .Column = ????

Can you help
0
 
asasakAuthor Commented:
jeffmeyn,

If .Column = colRng.Column Then

Not sure about setting up the range.  If .Column = ????

Can you help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.