Solved

Copy/Paste a checkbox in Excel

Posted on 2002-05-31
21
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
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…

735 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