Solved

Copy/Paste a checkbox in Excel

Posted on 2002-05-31
21
730 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

805 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