Print all items in a combo box

mbtaichi

Board Regular
Joined
Jan 5, 2016
Messages
71
Hi,
I have a combo box in cell B2 with a list of names.
Is there a Vba code to loop through the list, update the sheet from the combo box and print the worksheet.
I have over 100 names in the list and would like to run though the list updating the sheets and printing all.
Thanks
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
With a little guidance from https://stackoverflow.com/questions/51170356/excel-vba-for-each-loop-with-data-validation-lists
Code:
Sub LoopThroughDropdownAndPrint()
    Dim validationRange As Range
    Dim dropDownCell As Range
    
    Set dropDownCell = Range("B2")
   
    On Error Resume Next
    Set validationRange = Evaluate(dropDownCell.Validation.Formula1)
    On Error GoTo 0
    If Not validationRange Is Nothing Then
        'Validation list is based on a range
        Dim c As Range
        For Each c In validationRange
            dropDownCell.Value = c.Value
            ActiveSheet.PrintOut
        Next
    Else
         'Validation list is based on a fixed string
        Dim a As Variant, i As Integer
        a = Split(dropDownCell.Validation.Formula1, ",")
        For i = 0 To UBound(a)
            dropDownCell.Value = a(i)
            ActiveSheet.PrintOut
        Next
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,106
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top