Solved

Copy/Paste a checkbox in Excel

Posted on 2002-05-31
21
720 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:asasak
  • 11
  • 7
  • 2
  • +1
21 Comments
 
LVL 15

Expert Comment

by:dbase118
ID: 7046707
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
 

Author Comment

by:asasak
ID: 7046857
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
 
LVL 15

Expert Comment

by:dbase118
ID: 7046867
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
 
LVL 2

Expert Comment

by:TrueDrake
ID: 7046942
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
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7046960
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
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7046966
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
 

Author Comment

by:asasak
ID: 7047251
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
 

Author Comment

by:asasak
ID: 7047266
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
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7047336
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
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7047361
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:asasak
ID: 7047387
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
 

Author Comment

by:asasak
ID: 7047427
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
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7047490
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
 

Author Comment

by:asasak
ID: 7047522
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
 
LVL 2

Accepted Solution

by:
jeffmeyn earned 200 total points
ID: 7047564
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
 

Author Comment

by:asasak
ID: 7047605
The sheet is protected.  I will have to try another way.  Maybe in access.

Thanks for your excellent help
0
 
LVL 2

Expert Comment

by:jeffmeyn
ID: 7047627
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
 

Author Comment

by:asasak
ID: 7047914
Thanks jeffmeyn

I will try this tonight...
0
 

Author Comment

by:asasak
ID: 7049486
jeffmeyn,

If .Column = colRng.Column Then

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

Can you help
0
 

Author Comment

by:asasak
ID: 7049495
jeffmeyn,

If .Column = colRng.Column Then

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

Can you help
0
 

Author Comment

by:asasak
ID: 7049518
jeffmeyn,

If .Column = colRng.Column Then

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

Can you help
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now