Results 1 to 5 of 5

Thread: Looping through a combo box
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looping through a combo box

    Hello
    I use a combo box (form control) to cycle through a list of employees. When I click on one of the names a statement pulls up their information and I am able to print them. In the past I have just gone down the line and clicked each individual name. The amount of names has increased and I now need to create a loop to cycle through each name. Any help you could provide is greatly appreciated. Let me know if I can provide any further information to help. Thanks!

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Looping through a combo box

    Show me the script you have that does this:
    You said:
    When I click on one of the names a statement pulls up their information and I am able to print them.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    New Member
    Join Date
    Apr 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping through a combo box

    Quote Originally Posted by My Aswer Is This View Post
    Show me the script you have that does this:
    You said:
    When I click on one of the names a statement pulls up their information and I am able to print them.
    There isn't really script for the statement populating. The Cell link of the combo box outputs a number which is used to reference the employee ID and I have vlookups pulling in the appropriate data to the statement. The macro that I use to print and email the statements is below, but it is working fine, just looking to add a loop so that I click the button once and it cycles through the combo box list.

    Code:
    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

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Looping through a combo box

    This is beyond my knowledgebase.
    I will continue to monitor this thread to see what I can learn.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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