VBA ActiveX Controls ListBox - For-Next Loop not looping through multiple selected items

Question

Hello!

I have a problem. I am running an iteration macro within another iteration macro. The first iteration macro uses (or, is supposed to use) an ActiveX List Box to determine which items to loop through (here, they are called "Sequences"). The second iteration macro loops through a date range.

My problem lies with the first macro. I want it to loop through a series of "Sequences" that are chosen by the user. However, every time I run my code, the iteration stops after only 1 iternation. I.e., the only "Sequence" that the macro uses is the first one selected. The code is below"

Sub Iteration_Sequences()

On Error GoTo Errorcatch

         Dim i As Long

          With Sheets("Data Storage").ListBox_Sequences

              For i = 0 To .ListCount

                 If .Selected(i) Then

                     Sheets("Data Storage").Range("C3").Value = i

                     Application.Run "Iteration_Dates"

                 End If

              Next i

           End With

         Exit Sub

         Errorcatch:

         MsgBox Err.Description

Here is my code for the second iteration macro:

Sub Iteration_Dates()()

Sheets("Data Storage").Select

On Error GoTo Errorcatch

For i = Sheets("Data Storage").Range("D1").Value To Sheets("Data Storage").Range("D2").Value

    Sheets("Data Storage").Range("D4").Value = i

    Application.Run "Transfer"

Next i

Exit Sub

Errorcatch:

MsgBox Err.Description

End Sub

I know the code makes it all the way through both macros, because I have more code at the end of the "Iteration_Sequences" macro that is always executed at the end.

Any ideas?

Thanks!

Parker

Answer 1

I haven't set up a full test environment but there is s problem with the following.

For i = 0 To .ListCount

The .Selected(i) is a zero-based index. This mean that if the .ListCount is 3*, i* is going to be 0, 1, 2 never 3. It seems to me that is is going to throw an error every time that loop is processed whether something is selected or not. Try it as,

For i = 0 To .ListCount-1

Addendum: use Call rather than Application.Run and the name of the second sub doesn't have to be in quotes.

Call Iteration_Dates

Sub Iteration_Dates()

Answer 2

Ahh. I see now. Yep...didn't understand that the first time. 

Thanks for the follow-up.

Answer 3

Please go back and reread my reply. The problem is not with what you do with the value in C3. The problem is that you are always trying to do one more than selected. If there was nothing selected then .ListCount would be 0. In a zero-based .Selected your code would try to find the .Selected at least once. It shouldn't try it at all. The correct way to write that loop starts with,

For i = 0 To .ListCount-1

Answer 4

Thanks for the tip! My formula that looks at the output cell (output cell being C3 here, I believe) has a +1 to take care of that. 

Use "Call" rather than "Application.Run". Noted. Thanks for that!