Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Computers for Beginners (https://www.askmehelpdesk.com/forumdisplay.php?f=79)
-   -   Need VBA syntax for looping through checkboxes with IF statements (https://www.askmehelpdesk.com/showthread.php?t=347238)

  • Apr 28, 2009, 01:12 PM
    ITstudent2006
    Need VBA syntax for looping through checkboxes with IF statements
    The title explains it all.

    I need the command to loop through all of the checkboxes. There is also an IF command as well.

    Let me know!

    Rick
  • Apr 28, 2009, 01:58 PM
    Perito

    I would create an array of Boolean values. I would set the array, initially, to the state of the checkboxes, true for Checked and false for unchecked

    Dim BoolArray(n) as boolean

    ' on initialization
    for I = 0 to n-1
    BoolArray(I) = True
    next I

    Double-click each checkbox to create a separate "handler" subroutine unique to that checkbox. In the routine, set the BoolArray variable to the state of the checkbox:

    Private Sub CheckBox1_Click()
    BoolArray(0) = Checkbox1.Checked ' true if checked, false if not
    End Sub

    If I were doing this in Visual Basic or Visual Basic.NET, I would use the "TAG" property of the checkbox. I would put a unique number in each TAG and use that to set the Boolean array value. I'm not sure if this can be done in VBA, so that's why I'm hedging here. If you can get a reference to the checkbox, you can pick its TAG property and use that as an index into BoolArray. This would allow you to use one "handler" subroutine to fill the BoolArray array. Maybe you can figure that out.

    In any case, when you click on a "Proceed" button, or something similar, all you have to do is

    For I = 0 to n-1
    If BoolArray(I) then
    ... process here.
    end if
    Next I

    --------------------

    In Visual Basic (version 6 or before), they had the concept of "control arrays". I'm not sure if they have that in VBA or not. If they do, it's a simple matter of looping through using the name you gave to the control array and indexing it. Hit KF1> and look up "control arrays".

    In Visual Basic.NET, they've thrown out the concept of a control array (I never liked it anyway). What I do now (and, in fact, I do it in older VB programs because I dislike the way control arrays are handled) is create an array of controls:

    Dim CkBxArray(n) of CheckBox

    then, in an initialization routine, I initialize the array. It's a bit of a pain, but only a tiny pain:

    CkBxArray(0) = Checkbox1
    CkBxArray(1) = Checkbox2
    CkBxArray(2) = Checkbox3
    CkBxArray(3) = Checkbox4
    CkBxArray(4) = Checkbox5
    CkBxArray(5) = Checkbox6
    CkBxArray(6) = Checkbox7
    ...

    You can then use CkBxArray to evaluate the .Checked property.
  • Apr 28, 2009, 02:53 PM
    ITstudent2006

    I just need 1 simple command. A code that knows to loop through each checkbox and if it's true send it to the designated place.
  • Apr 28, 2009, 03:01 PM
    Perito

    It's not that simple. You need to create the array to access each instance of the checkbox. That's what I showed you how to do.
  • Apr 28, 2009, 03:19 PM
    ITstudent2006

    I didn't mean to push your answer aside. I am just not the one who wrote the syntax for what we've got so far. I am new to VBA so your response looked like Chinese to me. I am unsure if this was done already or not.

    Are you quite familiar with VBA (not VB.Net or VB) syntax. If so I will try and get what we've got so far and post it on here and you can add your input!

    Rick
  • Apr 28, 2009, 05:36 PM
    Perito

    I am fairly familiar with VBA. I've written a number of VBA projects throughout the years. I've had to work more in VB and in VB.NET so my real strength is there -- at least insofar as Basic languages are concerned. I've also programmed in C, C++, C#, Pascal, several assembly languages, and half a dozen or more not-so-well-known languages.

    The VBA syntax is not very different from VB or VB.NET, but there are enough differences that one sometimes runs into roadblocks that have to be worked through. If your VBA programmer can create a "control array" of these checkboxes, then it's very straight-forward to loop through the boxes.

    For I = 0 to numberofcheckboxes - 1
    if checkboxes(I).checked then
    ... do what you want to do with each checkbox here -- one checkbox at a time.
    end if
    next I

    If he can't, then I recommend creating an array to hold references to the checkboxes so you can easily loop through them. The loop is essentially the same except instead of using the exact syntax on the IF line, you have to use something else. That's basically what I'm trying to convey.
  • Apr 28, 2009, 05:38 PM
    ScottGem

    VBA is different for each Application. Word VBA is different from Access VBA which is different from Excel VBA.

    So you need to specify.
  • Apr 29, 2009, 07:55 AM
    ITstudent2006

    Yeah, I realize thae VBA is different per Application. But, no worries, we figured it out.

    Thanks anyway guys!

    Rick

  • All times are GMT -7. The time now is 03:08 AM.