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
JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 31, 2019

wcdf00 Yes that is certainly possible. But I would need the Excel file in question. Please replace sensitive information with some nonsense first.

wcdf00's avatar
wcdf00
Copper Contributor
Oct 31, 2019

JKPieterse 

Thanks a lot for your kindness, it means a lot to me. Hereby attach the file that already content some sample data.

 

But actually I would like to learn more on this than just push all the job to you. Will it be too much if I can ask you to show me the steps to set up the excel? If so, please forgive me.

 

Thanks again, Gob Bless You.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    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
    • hernan39's avatar
      hernan39
      Copper Contributor
      Sep 01, 2023
      Hello sir Jan. Thank you for sharing your knowledge. I am also a teacher in the Philippines who is also looking for this kind of solution. I tried your code but I always run into problem like Run-time Error '438' Object doesn't support this property or method. May I know how to fix this one?
    • amnaida's avatar
      amnaida
      Copper Contributor
      Jul 13, 2021

      Hi, JKPieterse 

       

      I'm also a teacher and is using the same excel file for my class. Is there any macro for me to save the data from the dropdown menu to separate pdf files instead of printing them. I've tried using the macro with print to pdf but I have to manually rename and and hit save every time the save as dialogue box appear. 

      Thanks in advance.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jul 13, 2021

        amnaida That is simple enough. The code below takes the selected value in the dropdown and uses that as the filename of the pdf. Make sure you edit the code below so it contains the correct path where to save the pdf files:

        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.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                                "C:\ChangePathHere\" & .List(.Value) & ".pdf", Quality:=xlQualityStandard, _
                                                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                                                False
            End If
        Next
    End With
End Sub

         

    • wcdf00's avatar
      wcdf00
      Copper Contributor
      Nov 03, 2019

      JKPieterse  Thanks, will try to apply it tomorrow. Thank you so much.

Resources