Forum Discussion

wcdf00's avatar
wcdf00
Copper Contributor
Oct 30, 2019
Solved

How to print out all item inside the drop down box (picture included)

Hello all, I am new here. Last few years I search through online to seek for a solution to auto print out all the option included in a drop down list ( which the content will change based on the drop...
developer
excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Oct 31, 2019

    wcdf00 YOu could use a macro like this to print all items in the 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
ikhwanizyan's avatar
ikhwanizyan
Copper Contributor
Aug 29, 2022

hello there, im also the same line as them, but can you show me how to use the code? a bit at lost here at the moment as i need to print the same file as them 

 

JKPieterse's avatar
JKPieterse
Silver Contributor
Aug 29, 2022

ikhwanizyan I can help but I need to know more details about your worksheet

  • tuannguyen81's avatar
    tuannguyen81
    Copper Contributor
    Apr 17, 2023

    Hi JKPieterse ,

     

    Please help me with the similar task:

    I have dropdown list in cell B12. I would like to print sheet "Summary page" based on value of dropdown list in B12.

    But I want it prints automatically to pdf with the name of each pdf file in format: VOC-abc which abc is the value of dropdown list, for example: VOC-Wes Jones.pdf

    Thanks for your help!

  • Thanks_for_your_help's avatar
    Thanks_for_your_help
    Copper Contributor
    Dec 22, 2022

    JKPieterse 

    The following is what i use:

    Sub Iterate_Through_data_Validation()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    Set xRg = Worksheets("term 1 Statement1 (17)").Range("m18")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    For Each xCell In xRgVList
    xRg = xCell.Value
    Selection.PrintOut
    Next
    End Sub

     

    The problem with this is that it prints and I want to save as pdf, I saw you have a solution but I could not get to use your code I wasn't sure what exactly needs to be changed it should match with my excel, so if you could be so kind and show me what to change in my code to save as separate pdf

     

     

    Thanks

Resources