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.
      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Apr 02, 2023

        f_osolo75 

        Here is an example with step by step instracition (see the file too):

        Create a named range for the cells that contain the drop-down list options. To do this, select the cells that contain the options and then click on the “Formulas” tab. In the “Defined Names” group, click on “Define Name” and enter a name for the range.

        Create a drop-down list in a cell by going to the “Data” tab and clicking on “Data Validation” in the “Data Tools” group. In the “Data Validation” dialog box, select “List” in the “Allow” drop-down list. In the “Source” field, enter the name of the named range that you created in step 1.

        Create a VBA macro that uses the value of the cell with the drop-down list to determine which print area to use. Here is an example of such a macro:

        Sub PrintSelectedRange()
        'Get the value of the cell with the drop-down list
        Dim selectedOption As String
        selectedOption = Range("A1").Value
        'Select the print area based on the selected option
        If selectedOption = "Examination" Then
        Range("C9:R86").Select
        ElseIf selectedOption = "score" Then
        Range("W4:AB46").Select
        End If
        'Set the print area to the selected range
        With ActiveSheet.PageSetup
        .PrintArea = Selection.Address
        End With
        'Print the selected range
        ActiveSheet.PrintOut
        End Sub

        This macro gets the value of the cell with the drop-down list (in this example, cell A1) and uses it to determine which range of cells to select as the print area. You can modify this code to match your specific needs.

         

        I hope this helps!

Resources