SOLVED

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

New Contributor

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. excel.jpg

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.

12 Replies

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

@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.

best response confirmed by wcdf00 (New Contributor)
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

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

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.

@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

 

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

@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

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

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
Tenkeu very much sir. Ur are very very help me. Thx

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