quicker way to link check boxes to cells

themole

Board Regular
Joined
Oct 26, 2009
Messages
96
hi,

i've got 70 names each with 6 check boxes (420 check boxes all together).

is there an easier way to link each box to cell with out doing one by one.

is there a way as such as doing one persons 6 check boxes and doing some sort of copy/paste or dragging cells to achieve what i want
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
The short answer is no, but I think the main question is why would you want to do this in the first place?

420 checkboxes.:eek: That just sounds like a nightmare to deal with.

Have you considered other approachs?
 
Upvote 0
Agree with Norie...

Consider just putting X's or something in a cell to mark the column true or false for each row..
 
Upvote 0
First, i agree with the above two posts. I would get out Access for this one.
But if you are content on using excel here is a bit of a shortcut


First select the data you want linked then run this macro

Code:
Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Offset(0, 3).Left, c.Top, c.Width, c.Height).Select
            With Selection
                .LinkedCell = c.Address
                .Characters.Text = ""
                .Name = c.Address
            End With
        Next
        myRange.Select
End Sub
I offset the check box by 3 cells, if you want another one it the Column over, either put a loop in this, or just change this code to 4, 5, and so on.

Code:
ActiveSheet.CheckBoxes.Add(c.Offset(0, 3).Left, c.Top, c.Width, c.Height).Select
Hope this helps, and in case you have like 100 check boxes already and want to erase them. Try this. (Id recommend saving a copy before trying this)

Code:
Sub RemoveCheckboxes()
On Error Resume Next
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
End Sub
 
Upvote 0
Ditto on what Norie and Jonmo1 said!!!!
You can use Simulated CheckBoxes
Rich (BB code):
Option Explicit 
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 
 'Limit Target count to 1
If Target.Count > 1 Then Exit Sub 
'Isolate Target to a specific column
if Target.Column <> 5 Then Exit Sub'set Target font to "marlett"
Target.Font.Name = "marlett" 
'Check value of target
If Target.Value <> "a" Then 
Target.Value = "a" 'Sets target Value = "a"
ElseIf Target.Value = "a" Then 
Target.ClearContents 'Sets Target Value = ""
End If 
End Sub
See CheckBox Alternative
<AHREF="HTTP: getarticle.php?kb_id='879"' kb www.vbaexpress.com>This will populate the cells with an "a" when checked and "" when not. You can then use an IF formula to check the value

lenze
 
Upvote 0
This worked far better that the actual checkboxes. Thanks. I do have a question however.

The code:
<code>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub
</code>

only provides for one range. What if I have two columns I want to have set to double click. I obviously can't use another Worksheet_BeforeDoubleClick sub without error. So how can I work two ranges into this code? The second range let call "FutureChecks"

any and all help is greatly appreciated!
 
Upvote 0

Forum statistics

Threads
1,215,412
Messages
6,124,761
Members
449,187
Latest member
hermansoa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top