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

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

25 Replies

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
  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.
  Student report template.xlsx348 KB
  - 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

Forum Discussion

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

25 Replies

Resources