Consulting

Results 1 to 18 of 18

Thread: Sleeper: Loop

  1. #1
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location

    Exclamation Sleeper: Loop

    Hi!

    I think I need your help again.
    I don't know what I'm doing wrong. It should retrieve the data from the system and print it. And go on to another organization for all the chosen organizations in the ListBox1.

    I'm thinking my code for the loop is incorrect.



    Dim i As Integer
    Dim Chosen As Integer
    Dim CurList As ListBox
    Dim UserForm As frm
    'show the tab
    Sheets("NON II").Select
    ' Initialize the Counter variable.
    For i = 0 To ListBox1.ListCount - 1
        'pick the right cells to populate
        If ListBox1.Selected(i) Then
            Worksheets("NON II").Range("F6") = ListBox1.List(i)
            Worksheets("NON II").Range("F9") = ComboBox1.Text
            Worksheets("NON II").Range("AE9") = ComboBox2.Text
            'close the form
            frm.Hide
            Sheets("NON II").Select
            Application.ScreenUpdating = True
    (This is where the loop should start)

           'Retrieve data for single organization 
            'RetrieveNON_II Range on NONII tab
            Sheets("Non II").Select
            esb_Retrieve9
            Sheets("NON II").Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        End If
        ' Increment the Organization Counter so we can get the value of the
        ' next selected item.
        ' Repeat until all the items in the list have
        ' been checked.
    Next
    End Sub

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Well, for looping through listboxes (as pointed out to me by Andy Pope) is best done backwards. So maybe your syntax should be something like ...

    Sub testMe() 'Enter listbox name and process, choose from the declarations drop down
        Dim i As Long
        On Error Resume Next
        For i = ListBox1.ListCount - 1 To 0 Step -1
            'what do you want done to your listbox here
        Next i
    End Sub
    This can be any value. It will step through your listbox and perform whatever you want (where it's marked) with that item. Another one going forward might be ...

    For i = 1 To ListBox1.ListCount
            'what do you want done to your listbox here
        Next i

    Btw, check out the VBA tags when posting (little green box just above your msg body), it will make your code look like mine does (we thank mark007 for that mod!).

  3. #3
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Quote Originally Posted by firefytr
    Well, for looping through listboxes (as pointed out to me by Andy Pope) is best done backwards.
    Why? With what reasoning?

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Oh, sorry. For deleting multiple listbox entries. I thought I had that in there, my bad. (Sorry 'bout the confustion.)

  5. #5
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    Hi!

    Sorry, it took me so long to respond... I was out . I think i got my loop going, but ... it performs functions within the loop only twice and then stops... I can't figure out what I did wrong... I have about 52 variables and I want to be able to pick and run more than two at the time... Desperate for help

    Private Sub OK_Click()
    Dim i As Variant
    Dim Chosen As Integer
    Dim CurList As ListBox
    Dim UserForm As frm
    'updated appl
    Application.ScreenUpdating = True
    'login to the system
    MsgBox "Please Sign-on to ""MRUSB"" DATABASE When Promted."
    STS = EssVConnect32(Null, gUsername, gPassword, "Ess02.us.bank-dns.com", "MRUSB", "Mrdata")
    If STS <> 0 Then Exit Sub
    'picking variables
    For i = 0 To ListBox1.ListCount - 1
    'defining cell location of the comboboxs
    If ListBox1.Selected(i) = True Then
    Worksheets("NON II").Range("F6") = ListBox1.List(i)
    Worksheets("NON II").Range("F9") = ComboBox1.Text
    Worksheets("NON II").Range("AE9") = ComboBox2.Text
    Worksheets("NON II").Range("f7") = ComboBox4.Text
    Worksheets("NON II").Range("f8") = ComboBox5.Text
    Sheets("Non II").Select
    'Retrieving data from the system
    'RetrieveNON_II Range on NONII tab
    Sheets("Non II").Select
    esb_Retrieve9
    'RetrieveExp Range on Exp tab
    Sheets("Exp").Select
    ' esb_Retrieve1
    'Print reports
    Sheets("NON II").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Exp").Select
    ' ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
    Next i
    frm.Hide
    Sheets("Non II").Select
    End Sub

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hey,

    Take a look at the VBA tags in the reply & quick reply box, they make your code look like mine above.

    Maybe you could try changing your For statement from
    For i = 0 To ListBox1.ListCount - 1
    to
    For i = ListBox1.ListCount - 1 To 0 Step - 1
    Plus your ranges don't seem to be changing with your loop, which means they will be getting written over for every step through your loop. Is that what you want?

  7. #7
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    Thanks!! I forgot about the VBA tags...

    I want my ranges to stay the same, the listbox1 is the only one that should change.

    Unfortunately your suggestion didn't work.. still runs only twice ;(((((

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    To check your loop, try this ...

    Private Sub OK_Click()
        Dim i As Integer, n As Integer
        Dim Chosen As Integer
        Dim CurList As ListBox
        Dim UserForm As frm
        'updated appl
        Application.ScreenUpdating = True
        'login to the system
        MsgBox "Please Sign-on to ""MRUSB"" DATABASE When Promted."
        STS = EssVConnect32(Null, gUsername, gPassword, "Ess02.us.bank-dns.com", "MRUSB", "Mrdata")
        If STS <> 0 Then Exit Sub
        'picking variables
        n = 0
        For i = 0 To ListBox1.ListCount - 1
            n = n + 1
            'defining cell location of the comboboxs
            If ListBox1.Selected(i) = True Then
                Worksheets("NON II").Range("F6") = ListBox1.List(i)
                Worksheets("NON II").Range("F9") = ComboBox1.Text
                Worksheets("NON II").Range("AE9") = ComboBox2.Text
                Worksheets("NON II").Range("f7") = ComboBox4.Text
                Worksheets("NON II").Range("f8") = ComboBox5.Text
                Sheets("Non II").Select
                'Retrieving data from the system
                'RetrieveNON_II Range on NONII tab
                Sheets("Non II").Select
                esb_Retrieve9
                'RetrieveExp Range on Exp tab
                Sheets("Exp").Select
                ' esb_Retrieve1
                'Print reports
                Sheets("NON II").Select
                ActiveWindow.SelectedSheets.PrintOut Copies:=1
                Sheets("Exp").Select
                ' ActiveWindow.SelectedSheets.PrintOut Copies:=1
            End If
        Next i
        MsgBox "You looped " & n & " number of loop(s)!", vbOKOnly + vbInformation, "Amount"
        frm.Hide
        Sheets("Non II").Select
    End Sub
    It appears your just skipping all the loops if the listbox item isn't selected. Are you sure that's what you want?

  9. #9
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    That is exactly what I need
    I want to loop only objects that I selected.

  10. #10
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    I think I figured out what the problem is, but I don't know how to fix it.
    the code executed too quickly for other parts of my application such as Print comand to keep up. How would I slow it down?

  11. #11
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I don't think that's the problem. The code will execute one line at a time. Try stepping through your code and/or using break points. Hit Alt + F11 to enter the Visual Basic Editor (VBE). Put your cursor in your code, then press F8 to step through your code 1 line at a time. Keep it at about a half-open screen and you should see what's happening to Excel in the back ground.

    What type of ListBox do you have, from the Forms toolbar? If so, is it a multi-select?

  12. #12
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    I have done it. It works fine when you step through the code since it slows it down. It won't execute when I press play button. I use the list box from the Forms toolbar and it is a multiple select. It lets me select as many variables as I like... The problem is: every loop it makes for the True chosen a lot of data is retrieved from the system, replaced and then printed.

    I need something to slow down the looping process...

  13. #13
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    To slow down your code try:


    Dim MyTimer As Double
    MyTimer = Timer
    Do
    Loop While Timer - MyTimer < 1 'Change as needed, this is one second.

  14. #14
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    still did work... I do appreciate your help, guys!

    Macro runs fine only when I disable this part of the code
    "ActiveWindow.SelectedSheets.PrintOut Copies:=1 ".

  15. #15
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Quote Originally Posted by firefytr
    For deleting multiple listbox entries.
    I still don't see the difference going forward or back will make.

  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    It's good practice. Like when you loop through a range of cells in a column and check for a condition. If found, row is deleted, that deleted row is shifted up, becoming the row number that you just deleted. Thus skipping that row in your loop as the task was already performed. If you go backwards, bottom to top, you don't run into that problem.

  17. #17
    VBAX Regular
    Joined
    Aug 2004
    Posts
    18
    Location
    HI!
    I've tried the code it looks perfect, and when I step through it, it works. However as soon as I start running the macro, while the second selected item from the list is chosen and it's printing all the selections in the listbox disappear. It works if only one of the functions within the loop're enabled (retrieve or print), but as soon as I make both enable it doesn't work properly.
    I was wondering if you can help me to redesigned the code in the following manner.

    1. Array all the choices within the listbox(list all the variables)

    2. loop to pick the ones that are chosen or selected from the form

    3. store the choices in the macro

    4. Run, retrieve and print only for the choices that are selected

    If I can, how would I do that or something similar?

    Thanks in advance

  18. #18
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    Quick question: Are you only getting 2 printouts or are you getting multiple printout but with only 2 of the sets of data?

    TJ
    Oh dear, I need a beer

Posting Permissions

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