Ask Experts Questions for FREE Help !
Ask
    ITstudent2006's Avatar
    ITstudent2006 Posts: 2,243, Reputation: 329
    Networking Expert
     
    #1

    Apr 28, 2009, 01:12 PM
    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
    Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #2

    Apr 28, 2009, 01:58 PM

    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.
    ITstudent2006's Avatar
    ITstudent2006 Posts: 2,243, Reputation: 329
    Networking Expert
     
    #3

    Apr 28, 2009, 02:53 PM

    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.
    Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #4

    Apr 28, 2009, 03:01 PM

    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.
    ITstudent2006's Avatar
    ITstudent2006 Posts: 2,243, Reputation: 329
    Networking Expert
     
    #5

    Apr 28, 2009, 03:19 PM

    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
    Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #6

    Apr 28, 2009, 05:36 PM

    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.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #7

    Apr 28, 2009, 05:38 PM

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

    So you need to specify.
    ITstudent2006's Avatar
    ITstudent2006 Posts: 2,243, Reputation: 329
    Networking Expert
     
    #8

    Apr 29, 2009, 07:55 AM

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

    Thanks anyway guys!

    Rick

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Computer looping [ 7 Answers ]

I am having trouble. We did a complete reinstall on Windows 2000 Professional. It starts the window 2000 set up, but at a certain point,starts allover again loading windows 2000. Ive tried taking the disk out, but it just keeps asking me where to boot from. Here is some of the messages I've...

How do I reboot my computer when it keeps looping and won't let me access the drives? [ 1 Answers ]

How do I reboot my computer when it keeps looping and won't let me access the drives?


View more questions Search