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

[wcdf00]

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 down list item) and I could not found an easy one as all the solution I found need to use some VBA language which I not really good at it.

Well since you read that much already, I will tell you the whole story. I am a teacher in Malaysia and we been given an excel template to record students' performance. The  PBD template report page is controlled by drop down box. Basically you choose a name and then the excel will use lookup function to search through the excel and give the value of the subject. 

My question is, is there anyway to print out all the option inside the drop down list by pressing one button? The algorithm should be like：

Print this page->click the drop down list->select the next item in drop down list -> then loop

This is how we do it if we do it manually. Properly, if and only if possible, I would like to do this:

Print this page->click the drop down list->select the next item in drop down list ->if next item is a blank, stop printing; else, loop.

There is like 30 to 40 students in a class, doing this save a lot of our time.

Hope that someone can help. Thanks in advance. God bless you and me.

[#2]

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

[#3]

@Jan Karel Pieterse 

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.

[#4 · Accepted solution]

@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

[#5]

@Jan Karel Pieterse  Thanks, will try to apply it tomorrow. Thank you so much.

[#6]

Hi, @Jan Karel Pieterse 

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.

[#7]

@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

[#8]

Works like magic! thanks a gazillion! You've just made my day =)

[#9]

@Jan Karel Pieterse 

Sorry sir i cant use ur code. I had debug at With ActiveSheet.DropDowns("Drop Down 4"). Can u help me ? Thx

[#10]

@morristyo This code assumes you have a drop-down (forms control, not activeX control!) on your sheet which is named "Drop down 4".

[#11]

Hi, thanks for your help. But i still have a little problem which is, the saved pdf file stops at drop down number 25. So is there anything i can do to change this? I have up to 38 students in my class. Thank u again 🙏🏼

[#12]

Tenkeu very much sir. Ur are very very help me. Thx

[#13]

@atiqahsahidah1989 Anything special about item number 25? Do you get an error?

[#14]

Hi @Jan Karel Pieterse

I got the same profession mentioned above and relatively same problem since now I know how to automatically print all documents from their respective drop-down list in one click. What I want to do next is print the documents back to back (sheet1 print to front and sheet2 to print to back page of the paper) since my printer is capable of printing automatically on 2-side of the paper.

*Note I am using the vlookup formula on both sheet1 and sheet2.
Is their anyway this could be possible?

[#15]

I don't think you can set that kind of printing features in VBA, but the trick I know works is to install the same printer two times and then change the printer settings for that "second printer" the way you want them (print both sides). Then select the other printer to do this print job.

[#16]

@Jan Karel Pieterse hi sir, I am unable to run this code in my sheet and found error of 400 and 1004.

[#17]

For this code to work you need a particular setup, please read the original post carefully

[#18]

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 

[#19]

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

[#20]

I'm trying to use this for a drop down list that is specifically in cell A2 (dropdown list is taken from a list on a seperate sheet and calle "=Overview!$B$4:$B$38".  I also want the pdfs collated as one file on the computer desktop.  Any help really appreciated.......not a regular code user.  Thank you @Jan Karel Pieterse