+ Reply to Thread
Results 1 to 5 of 5

For each Checkbox

  1. #1
    teresa
    Guest

    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



  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  3. #3
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:u5%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >
    > >

    >
    >




  4. #4
    Bill Linker
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:u5%[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >
    >


  5. #5
    Bill Linker
    Guest

    Re: 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1