CODEWITHSUNDEEP
excelvbaloops
Geoff
Geoff

Reputation: 109

How to Loop thru Listbox and perform repeated task

My problem is trying to repeat my code for each entry in a listbox on my userform. The code below will do it for one entry, but i need to do it for each item in the list box. My ListBox looks like this.

'ListBox
PBB
ABH
GKB

This code opens a template workbook and saves it based on the name in the listbox. It then inputs data to the new workbook. Option Explicit Dim DIV As String Dim XL As Excel.Application Dim WBK As Excel.Workbook Dim ActSheet As Worksheet Dim ActBook As Workbook Dim CurrentFile As Workbook Dim NewFileType As String Dim NewFile As String Dim QTR_NUM As String Dim MFG As String Dim JOB As String Dim visitdate As Variant Dim visitdate_text As String Dim Quote_Request As Worksheet Dim QTR As Workbook Dim FORM As Workbook Dim DCSProgram As Workbook Dim ILast As Long Dim j As Integer

Application.ScreenUpdating = False    ' Prevents screen refreshing.

Set DCSProgram = ActiveWorkbook


Set QTR = Workbooks.Open("C:\Users\Geoffrey\Dropbox\DCS PROGRAM\FILES\1. QUOTES\3. PROGRAM FILES\QTR.xlsx")

ActiveWindow.WindowState = xlMinimized


For j = 0 To QTRList.ListCount - 1

MFG = QTRList.List(j)

visitdate = Date
visitdate_text = Format$(visitdate, "mm\-dd\-yyyy")

QTR.SaveAs FileName:="C:\QTR" & "\DCS QTR " _
& " " & MFG & " " & JobName & " " & visitdate_text & " .xlsm", _
FileFormat:=52, CreateBackup:=False, local:=True

Application.ScreenUpdating = True

Set FORM = Workbooks.Open("C:\QTR" & "\DCS QTR " _
& " " & MFG & " " & JobName & " " & visitdate_text & " .xlsm")


With FORM.Sheets(1).Range("H9")
.Value = Date
.NumberFormat = "mm/dd/yy"
End With

FORM.Sheets(1).Range("H13").Value = JobName                   'JOB NAME

With DCSProgram.Sheets("MFG_DATA")
    ILast = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To ILast
        If .Cells(i, 1).Value = MFG Then
            FORM.Sheets(1).Range("B7").Value = .Cells(i, 2).Value
            FORM.Sheets(1).Range("B8").Value = .Cells(i, 3).Value
            FORM.Sheets(1).Range("B9").Value = .Cells(i, 4).Value
            FORM.Sheets(1).Range("B12").Value = .Cells(i, 5).Value
            FORM.Sheets(1).Range("B13").Value = .Cells(i, 6).Value
            FORM.Sheets(1).Range("B14").Value = .Cells(i, 7).Value
            FORM.Sheets(1).Range("B15").Value = .Cells(i, 8).Value

   End If: Next: End With
   Next j

   End Sub

Im trying to have the above code then repeat for the next item in listbox, ABH and GKB. If someone could point me in the right direction it would be much appreciated. Thanks Again, Geoff

Upvotes: 0

Views: 804

Answers (1)

Xavier Navarro
Xavier Navarro

Reputation: 36

You can loop through the listbox items using something like this:

For i = 0 To QTRList.ListCount - 1
   MsgBox (QTRList.List(i))
Next

Upvotes: 0

Related Questions