Autofill for checkboxes

[john]

In my spreadsheet I need 177 checkboxes all linked to the cell they reside
in. Is there a way to make one checkbox link it to the right cell and then
autofill the other 176 checkboxes? The way I'm doing it now is not working
as I have to make the link to the respective checkbox 176 times checkbox for
checkbox.
thanks,
john

 

[stevebriz]

do you want the cell to change when the checkbox is change or vice
versa?

 

[john]

I want the cell to change when the checkbox is changed and I have got that
working for one checkbox and the respective cell. Maybe Autofill isn't the
right word but I want to select the left-right-corner of the first cell with
the checkbox and drag it 176 cells further so that 176 checkboxes are made
and related to the cell they reside in.

 

[stevebriz]

You can do somethhing like this with macros enabled and the macro
below.

call this sub on the click event of each check box.
this works for sheet1..but you can change the name to another in the
code if you like.
(Ps it works for checkboxes on sheet1 - an they all update on every
click of a check box)

' eg for :
sub checkbox1_click()
call test1
End sub



'------- Macro--------------------
Sub test1()
Dim ctl As OLEObject
''Dim sPath As String
i = 1
'sPath = "Cocuments and SettingsDickMy DocumentsTester"

For Each ctl In Sheet1.OLEObjects

If TypeName(ctl.Object) = "CheckBox" Then
If ctl.Object.Value = True Then
Sheet1.Cells(i, 1).Value = "TRUE"
Else
If ctl.Object.Value = False Then Sheet1.Cells(i,
1).Value = "FALSE"
End If
End If
i = i + 1
Next ctl
End sub
'------------------END---------------------------------------------
'Hope this helps


End Sub

 

[Dave Peterson]

Saved from a previous post:

Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).

The second one is the one that would do what you want to do when you
check/uncheck that box.

The first one only needs to be run once--to set up the checkboxes on the
worksheet.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

If you don't need a procedure to run when you check/uncheck that checkbox, don't
include the "doTheWork" subroutine and delete the .OnAction line.

And change this line
For Each myCell In ActiveSheet.Range("B3:B10").Cells
to use the range that should get the checkboxes.

 

[john]

Thanks guys.
Unfortunately that's not exactly what I'm looking for.
Let's say I have linked the first checkbox in A1 to the value in A1 so if I
check and uncheck that checkbox the value toggles between true and false.
Now I would like to select the right-below corner of A1 and drag it to F1 in
a way that all cells relate to the checkbox that is in the respective cell
(checkbox in cell B2 relates to cell B2).
Another question: how can you select more than one checkbox and delete them?
john