Consulting

Results 1 to 2 of 2

Thread: Looping through combo box

  1. #1

    Looping through combo box

    Hi all,

    I am responsible for creating and distributing commission statements monthly to our sales force. Currently I have a Combo Box(Form Control) that has a list of every employee. I click on the individual names, the Cell Link puts the number that the name is in the list, which in turn pulls up their employee ID which I have vlookups pulling in the data for that particular employee. I then click a button which uses the code below and a statement is generated and emailed.

    The list of employees has grown to great to individually click each name and I need to create a loop to select the name, run the code below, and then move on to the next name. Thanks for your time!
    Sub WSRA_Button5_Click()
     Dim PDFFileName As String
        Dim PSFilename As String
        Dim LogFilename As String
        PSFilename = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V3")
        PDFFileName = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V4")
        LogFilename = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V7")
        'Print the Excel range to the pdf file
        ActiveSheet.Range("E5", "R72").PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, prtofilename:=PSFilename
        Dim myPDF As PdfDistiller
        Set myPDF = New PdfDistiller
        myPDF.FileToPDF PSFilename, PDFFileName, ""
        Kill PSFilename 'delete postscript temporary file
        Kill LogFilename 'delete log file
    Dim strrecipient As String
    Dim strstatement As String
    strstatement = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V4")
    strrecipient = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V5")
    Dim pathname As String
    Dim dname As String
    pathname = strstatement 'defines attachment
    dname = Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V2") 'defines date for subject
    Dim objol As Outlook.Application
    Dim objmail As Outlook.MailItem
    Set objol = New Outlook.Application
    Set objmail = objol.CreateItem(Outlook.olMailItem)
        With objmail
            .To = strrecipient 'enter in here the email address
            .Subject = dname
            .Body = "Please find a copy of your " & Workbooks("Canada commissions model 2017.xlsm").Sheets("WS-RA Statement").Range("V2") & " attached." & _
                vbCrLf & vbCrLf & "If you have any questions, please contact Ryan Daltry or your manager. A statement with greater details on your Annual Accelerator will come later this week. " & vbCrLf
            .NoAging = True
            .Attachments.Add pathname 'adds attachment to email
            .display
        End With
        Set objmail = Nothing
        Set objol = Nothing
        SendKeys "%{s}", True 'send the email without prompts
    End Sub

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Personally, I would loop thru the actual Employee list on the Worksheet and not even use a Combo Box.

    ComboBox loop code
    Dim i As Long
    For i = 0 to Combox.ListCount - 1
       EmployeeName = ComboBox.List(i)
    Next
    To return the value of the second column in a multicolumn Control
    For i = 0 to Combox.ListCount - 1
       EmployeeNumber = ComboBox.List(i, 2)
    Next
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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