Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for simple code
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 -- goeppng ----------------------------------------------------------------------- goeppngr's Profile: http://www.excelforum.com/member.php...fo&userid=3090 View this thread: http://www.excelforum.com/showthread.php?threadid=51732 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for simple code
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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "goeppngr" wrote in message ... 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 the code below. Can someone help me to write a function (I am kind of new to writing functions) that would take care of this in much more 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 Sub -- goeppngr ------------------------------------------------------------------------ goeppngr's Profile: http://www.excelforum.com/member.php...o&userid=30906 View this thread: http://www.excelforum.com/showthread...hreadid=517327 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Code, I think... | Excel Worksheet Functions | |||
Need code for simple IF-Then | Excel Programming | |||
Want a simple VBA code. | Excel Programming | |||
Simple Code Help | Excel Programming |