ListIndex with ComboBox

Long story short, I helped a coworker create a macro to print 100+ .pdfs from a excel template controlled by a combobox he created using the Control Toolbox bar, so I believe it's ActiveX.

I looked around online to figure out how to control the combobox so it would loop through the selections, but for the life of me I can not get .ListIndex to work. So I wasn't sure if it only works with a Forms ComboBox, or how I would control the ActiveX combobox?

I did it the "ugly" way by just changing the linked cell to the value in the table as I looped through, but I would also like to learn how to use ListIndex for the future.

The commented out line is my attempt at .ListIndex. I tried a few other methods that I found online using Me., Shapes and Dropdown("ComboBox1"), etc. but none of them seemed to work.

Sub Print_Agents()

Dim lCount As Long
Dim wbComp As Workbook
Dim wsComp As Worksheet
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim mypdfDist As New PdfDistiller

Set wbComp = ThisWorkbook
Set wsComp = wbComp.Sheets("SummaryAgt")

For lCount = 1 To wsComp.Range("T6", wsComp.Cells(Rows.Count, "T").End(xlUp)).Rows.Count
    'wsComp.ComboBox1.ListIndex = lCount - 1
    wsComp.Range("U3").Value = wsComp.Range("W" & lCount + 5).Value 'Workaround I used to control the ComboBox
    tempPDFRawFileName = "G:\Budgets\Budget Finance\Financial Analysis\Compensation Plans\Texas FSU ProPay II\" & Right(Replace(wsComp.Range("C2").Value, ",", ""), Len(wsComp.Range("C2").Value) - 4)
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    tempLogFileName = tempPDFRawFileName & ".log"
    
    wsComp.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
        
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
    Kill tempPSFileName
    Kill tempLogFileName
    
Next lCount
Set mypdfDist = Nothing

End Sub

Hello Strife,

I think you need to review your post. The code does not include any references to a ComboBox. As a general rule when working with ActiveX (Control Toolbox) controls, do not control them by links, use the control's event procedures instead. This will save you a lot a problems.

Hello Strife,

I am not trying to be a pain but can you explain in words what you want this code to do?

Originally Posted by Leith Ross

Hello Strife,

I am not trying to be a pain but can you explain in words what you want this code to do?

Sure,

I'm just trying to get the combobox to select each drop down selection as it goes through the loop. So currently the combobox dropdown has 95 agent's names, which controls a template which shows their production, pay, etc. The macro just goes through the loop and prints to adobe pdf each agent so they can be distributed. Basically saving time so my coworker doesn't have to individually select each name, print, name the file, etc.

Currently the macro works with my method of changing the actual cell linked to the combobox i.e. "U3", but I would like to learn the correct way to control the combobox so that I can have it loop from beginning to end without touching the actual linked cell "U3".

So i wasn't sure if I should use .ListIndex to change the number, i.e. 0 for the first name, 1 for the 2nd name, etc. or if it's something else? Because currently I can't get the line of code for wsComp.ComboBox1.ListIndex = lCount - 1 to work, it errors out.

Just trying to learn proper method and how to control an ActiveX ComboBox, thanks

Hello Strife,

So the purpose of the ComboBox is really nothing more than a visual indicator of the agent's name?

Originally Posted by Leith Ross

Hello Strife,

So the purpose of the ComboBox is really nothing more than a visual indicator of the agent's name?

Well it controls what shows up on the template as well. The whole template is built off the agent ID# which is part of the ComboBox, and has vlookups, etc. to a database tab. Just think of like a paycheck stub, if I change the name in the drop down, all of the pay, production, taxes, etc. all change based on that name.

All that stuff is fine, no problems with that. I guess to simplify my question since all of this other stuff doesn't really pertain to my problem is:

This line of code currently doesn't work because I get the error Method or Data Member not found

wsComp.ComboBox1.ListIndex = lCount - 1

What is the correct way to cycle through a ComboBox and select each name, do an action, then go to the next name in the list. I am obviously doing something wrong in the above code but I can't figure it out :/

Sorry for the confusion, I thought this was going to be a quick answer like oh, you need to add 'x' variable into your line of code.

I've tried a bunch of things like Me.ComboBox1.ListIndex, wsComp.Dropdown("ComboBox1").Listindex, .Shapes, etc. but none seem to work.

Sorry I left from work yesterday.

I tried your bit of code again but I'm still getting the 'Method or Data Member Not Found' error on this line in reference to combobox1:

For i = 0 To ComboBox1.ListCount - 1

I did realize though that there is no ComboBox object in VBA in my workbook even though I have a ComboBox on my worksheet. Usually when you make an ActiveX ComboBox does some bit of code show up in VBA also? Maybe that's why I can't reference ComboBox1... dunno.

Guess I'll just keep my current workaround for now and try to search around more.

Thanks for the help.

Edit: Okay I double clicked on the combobox to get this bit of code to show up on the worksheet I'm referencing but I still get an error.

Private Sub ComboBox1_Change()

End Sub

Sorry I'm really noobie with these ActiveX controls, do I need to insert some type of code directly into the private sub to be able to reference the combobox1 object in my code?

Hello Strife,

There are three commonly used drop downs: Validation Lists, Forms control, and Control Tollobox. The Validation will appear when you enter the cell. The easiest way to tell a Forms style from an Control Toolbox is by the cursor. A Forms control will change from a cross hair to a hand when you move over it. A Control ToolBox control will change from a cross hair to a arrow.

Hello Strife,

Now that we know it is a Control TollBox ComboBox, here is the code to reference it on a worksheet. You may need to change the worksheet name and ComboBox names.

    Dim Cbo As OLEObject
    Dim I As Integer
    Dim val As String
    
        Set Cbo = Worksheets("Sheet1").OLEObjects("ComboBox1")
        
        With Cbo.Object
            For I = 0 To .ListCount - 1
                val = .List(I)
            Next I
        End With

Hello Strife,

You need to set the ListIndex property to the loop value. Here is an example..

    Dim Cbo As OLEObject
    Dim I As Integer
    Dim val As String
    
        Set Cbo = Worksheets("Sheet1").OLEObjects("ComboBox1")
        
        With Cbo.Object
            For I = 0 To .ListCount - 1
                .ListIndex = I
            Next I
        End With

Hello Strife,

You're welcome. Thanks for marking the post solved.