Forum Discussion

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 02, 2023

f_osolo75 

You can use a macro to print all items in a drop-down list. 

Here is an example of a macro that can help you print all items in a list

Sub PrintAll()
    Dim lCt As Long
    With ActiveSheet.DropDowns("Drop Down 4")
        For lCt = 1 To .ListCount
            .ListIndex = lCt
            If Len(.List(.Value)) > 0 Then
                ActiveSheet.PrintOut
            End If
        Next
    End With
End Sub

You can modify this macro to suit your needs.

For example, you can change the name of the drop-down list from “Drop Down 4” to the name of your drop-down list.

You can also specify the range you want to print by adding a range argument to the PrintOut method.

 

 

I hope this helps! 

  • f_osolo75's avatar
    f_osolo75
    Copper Contributor
    Apr 02, 2023
    Thanks. How do I add a range argument to the PrintOut method?
    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Apr 02, 2023

      f_osolo75 

      Print by adding a range argument to the PrintOut method

      You can use a loop to iterate through the items in the drop-down list and print the selected range for each item.

      Here’s an example that assumes the drop-down list is a data validation list in cell A1 on Sheet1 and the range to print is B2:C3 on Sheet1.

      Sub PrintDropDownList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim val As Variant

    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1").Validation.Formula1
    rng = Right(rng, Len(rng) - 1)
    Set rng = ws.Range(rng)

    For Each cell In rng.Cells
        val = cell.Value
        ws.Range("A1").Value = val
        ws.Range("B2:C3").PrintOut
    Next cell
End Sub

      This code sets the value of cell A1 to each item in the drop-down list and then prints the range B2:C3.

      You can adjust the worksheet and range references to match your specific needs.

       

      I hope this helps!

      • f_osolo75's avatar
        f_osolo75
        Copper Contributor
        Apr 02, 2023
        I tried to run the code, but it responded with an error "type mismatch". The phrase ".Formula1" was also highlighted.

Resources