Looping through a combo box

ryandaltry

New Member
Joined
Apr 19, 2018
Messages
2
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top