For each Checkbox
 

Hi, I have 20 checboxes, and i need the same principle for each,

Sub checkbox1_click()

If CheckBox1.Value Then
Range("l6").Formula = "=K33"
Else
Range("l6").Value = 0
End If
End Sub


So I have so far:

For each checkbox in wks
If CheckBoxa.Value Then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If
End Sub

For each Checkbox
 

John Walkenbach shows how to have one event macro handle multiple controls.
As written, it is for commandbuttons on a userform, but you should be able
to adapt it to checkboxes on a worksheet

http://j-walk.com/ss/excel/tips/tip44.htm
"teresa" wrote in message
...
Hi, I have 20 checboxes, and i need the same principle for each,

Sub checkbox1_click()

If CheckBox1.Value Then
Range("l6").Formula = "=K33"
Else
Range("l6").Value = 0
End If
End Sub


So I have so far:

For each checkbox in wks
If CheckBoxa.Value Then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If
End Sub

For each Checkbox
 

Teresa,

I think it's a little more difficult than Tom suggests, and beyond your
current capabilities, so here are some steps.

First, add a class module, name it clsWsCtls, and add this code

Public WithEvents mCheckboxes As MSForms.CheckBox

Private Sub mCheckboxes_Click()
Dim iCb
If mCheckboxes.Value Then
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5).Formula =
"=K33"
Else
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5) = 0
End If
End Sub

Then put this code in your worksheet code module (get rid of your old code)

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim clsControls As clsWSCtls
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
Set clsControls = New clsWSCtls
Set clsControls.mCheckboxes = shp.OLEFormat.Object.Object
mcolEvents.Add clsControls
End If
End If
Next

End Sub

This technique depends upon the checkboxes being called CheckBox1,
CheckBox2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" wrote in message
...
John Walkenbach shows how to have one event macro handle multiple
controls.
As written, it is for commandbuttons on a userform, but you should be able
to adapt it to checkboxes on a worksheet

http://j-walk.com/ss/excel/tips/tip44.htm
"teresa" wrote in message
...
Hi, I have 20 checboxes, and i need the same principle for each,

Sub checkbox1_click()

If CheckBox1.Value Then
Range("l6").Formula = "=K33"
Else
Range("l6").Value = 0
End If
End Sub


So I have so far:

For each checkbox in wks
If CheckBoxa.Value Then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If
End Sub

For each Checkbox
 

I use the Form's Controls collections and the ability to create a string
index into the collection when I have a problem like this. If you just want
to check all 20 all at once, have an OK or Done button and in the
Button_Click() event have something like (assuming the name of your UserForm
is "formName"):


For itemNum = 1 to 20
if (formName.Controls("CheckBox" & itemNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

Next itemNum

If you want to respond to each individual click on a checkbox, do it like this

Sub checkbox1_click()
processCBclick(1)
End Sub

Sub checkbox2_click()
processCBclick(2)
End Sub
.....
....
Sub checkbox20_click()
processCBclick(20)
End Sub


Sub processCBclick(cbNum as Integer)

if (formName.Controls("CheckBox" & cbNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

End Sub


To use this you just have to be consistent about naming your controls. You
can expand it to multiple dimension too, though this would probably only
popup in some sort of game:

Sub CheckBoxR1C1_click()
processCBclick(1, 1)
end Sub

Sub CheckBoxR5C3_click()
processCBclick(5,3)
end Sub

Sub processCBclick(rowNum as Integer, colNum as Integer)

if (formName.Controls("CheckBoxR" & rowNum & "C" & colNum).Value) then
'do something
else
'do something else
end if

end Sub




"Bob Phillips" wrote:

Teresa,

I think it's a little more difficult than Tom suggests, and beyond your
current capabilities, so here are some steps.

First, add a class module, name it clsWsCtls, and ad d this code

Public WithEvents mCheckboxes As MSForms.CheckBox

Private Sub mCheckboxes_Click()
Dim iCb
If mCheckboxes.Value Then
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5).Formula =
"=K33"
Else
iCb = Mid(mCheckboxes.Caption, 9, Len(mCheckboxes) + 1)
Worksheets(mCheckboxes.Parent.Name).Range("l" & iCb + 5) = 0
End If
End Sub

Then put this code in your worksheet code module (get rid of your old code)

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim clsControls As clsWSCtls
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
Set clsControls = New clsWSCtls
Set clsControls.mCheckboxes = shp.OLEFormat.Object.Object
mcolEvents.Add clsControls
End If
End If
Next

End Sub

This technique depends upon the checkboxes being called CheckBox1,
CheckBox2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" wrote in message
...
John Walkenbach shows how to have one event macro handle multiple
controls.
As written, it is for commandbuttons on a userform, but you should be able
to adapt it to checkboxes on a worksheet

http://j-walk.com/ss/excel/tips/tip44.htm
"teresa" wrote in message
...
Hi, I have 20 checboxes, and i need the same principle for each,

Sub checkbox1_click()

If CheckBox1.Value Then
Range("l6").Formula = "=K33"
Else
Range("l6").Value = 0
End If
End Sub


So I have so far:

For each checkbox in wks
If CheckBoxa.Value Then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If
End Sub

For each Checkbox
 

If you need to shift the range and formula items as well within your if-else
tree, you can use R1C1 reference style to make it easier to use the checkbox
number to offset the target ranges. So, if row 3, column 3 is your starting
point trying something like:

BASEROW = 3
BASECOL = 3

if (formName.Controls("CheckBox" & itemNum).Value) then
targetRow = BASEROW + itemNum - 1 '
Range("R" & targetRow & "C3").Formula = "=$R5$C" & (BASECOL + itemNum)
Else

In above, the Left side of the equation varies by row, but is fixed in
column 3 while in the right side, the Row is constant, but the column varies.

"Bill Linker" wrote:

I use the Form's Controls collections and the ability to create a string
index into the collection when I have a problem like this. If you just want
to check all 20 all at once, have an OK or Done button and in the
Button_Click() event have something like (assuming the name of your UserForm
is "formName"):


For itemNum = 1 to 20
if (formName.Controls("CheckBox" & itemNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

Next itemNum

If you want to respond to each individual click on a checkbox, do it like this

Sub checkbox1_click()
processCBclick(1)
End Sub

Sub checkbox2_click()
processCBclick(2)
End Sub
....
...
Sub checkbox20_click()
processCBclick(20)
End Sub


Sub processCBclick(cbNum as Integer)

if (formName.Controls("CheckBox" & cbNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

End Sub


To use this you just have to be consistent about naming your controls. You
can expand it to multiple dimension too, though this would probably only
popup in some sort of game:

Sub CheckBoxR1C1_click()
processCBclick(1, 1)
end Sub

Sub CheckBoxR5C3_click()
processCBclick(5,3)
end Sub

Sub processCBclick(rowNum as Integer, colNum as Integer)

if (formName.Controls("CheckBoxR" & rowNum & "C" & colNum).Value) then
'do something
else
'do something else
end if

end Sub