Function for simple code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

goeppngr - 28 Feb 2006 15:11 GMT

I am writing code for multiple checkboxes. When each one is selected i
grabs a value for a certain cell from another worksheet. I am pastin
the code below. Can someone help me to write a function (I am kind o
new to writing functions) that would take care of this in much mor
precise code? I could just call the function six times. Thanks.

Private Sub cmdCheckBox1_Click()
Select Case cmdCheckBox1.Value
Case Is = True
Sheet1.Range("h15").Value = Sheet2.Range("c2").Value

Case Is = False
Sheet1.Range("h15").Value = Sheet2.Range("b2").Value

End Select

End Sub

Private Sub cmdCheckBox2_Click()
Select Case cmdCheckBox2.Value
Case Is = True
Sheet1.Range("h20").Value = Sheet2.Range("c3").Value

Case Is = False
Sheet1.Range("h20").Value = Sheet2.Range("b3").Value

End Select
End Sub

Private Sub cmdCheckBox3_Click()
Select Case cmdCheckBox3.Value
Case Is = True
Sheet1.Range("h25").Value = Sheet2.Range("c4").Value

Case Is = False
Sheet1.Range("h25").Value = Sheet2.Range("b4").Value

End Select
End Sub

Private Sub cmdCheckBox4_Click()
Select Case cmdCheckBox4.Value
Case Is = True
Sheet1.Range("h35").Value = Sheet2.Range("c5").Value

Case Is = False
Sheet1.Range("h35").Value = Sheet2.Range("b5").Value

End Select
End Sub

Private Sub cmdCheckBox5_Click()
Select Case cmdCheckBox5.Value
Case Is = True
Sheet1.Range("h40").Value = Sheet2.Range("c6").Value

Case Is = False
Sheet1.Range("h40").Value = Sheet2.Range("b6").Value

End Select
End Sub

Private Sub cmdCheckBox6_Click()
Select Case cmdCheckBox6.Value
Case Is = True
Sheet1.Range("h45").Value = Sheet2.Range("c7").Value

Case Is = False
Sheet1.Range("h45").Value = Sheet2.Range("b7").Value

End Select
End Su

Reply to this Message

Bob Phillips - 28 Feb 2006 16:14 GMT

Private Sub cmdCheckBox1_Click()
ProcessCheckbox cmdCheckBox1, Sheet1.Range("H15"), _
Sheet2.Range("C2"), Sheet2.Range("B2")
End Sub

Private Sub cmdCheckBox2_Click()
ProcessCheckbox cmdCheckBox2, Sheet1.Range("H20"), _
Sheet2.Range("C3"), Sheet2.Range("B3")
End Sub

Private Sub cmdCheckBox3_Click()
ProcessCheckbox cmdCheckBox3, Sheet1.Range("H25"), _
Sheet2.Range("C4"), Sheet2.Range("B4")
End Sub

Private Sub cmdCheckBox4_Click()
ProcessCheckbox cmdCheckBox4, Sheet1.Range("H35"), _
Sheet2.Range("C5"), Sheet2.Range("B5")
End Sub

Private Sub cmdCheckBox5_Click()
ProcessCheckbox cmdCheckBox5, Sheet1.Range("H40"), _
Sheet2.Range("C6"), Sheet2.Range("B6")
End Sub

Private Sub cmdCheckBox6_Click()
ProcessCheckbox cmdCheckBox6, Sheet1.Range("H45"), _
Sheet2.Range("C7"), Sheet2.Range("B7")
End Sub

Private Sub ProcessCheckbox(cb As MSForms.CheckBox, rngFrom As Range, _
rngToTrue As Range, rngToFalse As Range)
If cb Then
rngFrom.Value = rngToTrue.Value
Else
rngFrom.Value = rngToFalse.Value
End If
End Sub

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I am writing code for multiple checkboxes. When each one is selected it
> grabs a value for a certain cell from another worksheet. I am pasting
[quoted text clipped - 68 lines]
> End Select
> End Sub

Reply to this Message