Oct 30 2019 04:39 PM
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.
Oct 31 2019 12:43 AM
@wcdf00 Yes that is certainly possible. But I would need the Excel file in question. Please replace sensitive information with some nonsense first.
Oct 31 2019 05:51 AM
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.
Oct 31 2019 09:19 AM
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
Nov 02 2019 11:38 PM
@Jan Karel Pieterse Thanks, will try to apply it tomorrow. Thank you so much.
Jul 13 2021 01:03 AM - edited Jul 13 2021 01:06 AM
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.
Jul 13 2021 02:35 AM
@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
Jul 13 2021 03:29 AM
Jul 28 2021 08:04 PM
@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
Jul 29 2021 01:20 AM
@morristyo This code assumes you have a drop-down (forms control, not activeX control!) on your sheet which is named "Drop down 4".
Aug 01 2021 10:26 AM
Aug 01 2021 08:39 PM
Aug 04 2021 02:24 AM
@atiqahsahidah1989 Anything special about item number 25? Do you get an error?
May 28 2022 12:09 AM
May 30 2022 01:43 AM
Aug 26 2022 07:08 PM
@Jan Karel Pieterse hi sir, I am unable to run this code in my sheet and found error of 400 and 1004.
Aug 27 2022 01:03 AM
Aug 28 2022 09:29 PM
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
Aug 29 2022 10:46 AM
@ikhwanizyan I can help but I need to know more details about your worksheet
Oct 09 2022 05:09 AM
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
Oct 31 2019 09:19 AM
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