Oct 30 2019 04:39 PM
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 AMSolution
@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 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 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