Data validation list for-loop wont go to next

[opsintern]

Hi guys,

I've got a data validation list named 'DVLDriver' which has a few macros linked to it (meaning the macros do different things based on the currently selected item). My code needs to run a few macros for every item in the DVLDriver list, but for some reason only does the first item. I've tried looking on the internet and on the forum for some help, but it's hard to find anything that works.

The other macros I need it to run have already been coded and shouldn't be the problem as they are executed (but just for the first item).

My code is as follows:

Sub LoopThroughValidationList() Dim wsReport As Worksheet
Dim rngState As Range

Set wsReport = Worksheets("Comments") 'source worksheet

Application.ScreenUpdating = False
For Each rngState In Sheets("Comments").Range("DVLDriver") 'loop through list
wsReport.Range("DVLDriver").Value = rngState 'select state

Call ResetFilters
Call ApplyDateFilter
Call ApplyDriverFilter
Call CopyFilteredTable
Call ExportToPDF
Next
Application.ScreenUpdating = True

End Sub

[Paul_Hossler]

Not tested and making some assumptions

It might be easier if you passed the cell or value to each of the subs




Sub LoopThroughValidationList() Dim wsReport As Worksheet
Dim rngState As Range, rngCell as Range

Set wsReport = Worksheets("Comments")

Application.ScreenUpdating = False

For Each rngCell In wsReport.Range("DVLDriver").Cells

Call ResetFilters (rngCell)
Call ApplyDateFilter (rngCell)
Call ApplyDriverFilter (rngCell)
Call CopyFilteredTable (rngCell)
Call ExportToPDF (rngCell)

Next

Application.ScreenUpdating = True

End Sub

[opsintern]

Hi Paul,

I've tried running the code you gave me and it gave me a compile error: Wrong number of arguments or invalid property assignment. I think this might've to do with the (rngCell) after each macro, as your macro works when I removed that. However, it only works for the value that is currently selected in the data validation list. I still don't get more than one PDF exported (the one from the person that's selected from the list).

Any ideas on how to fix that?

[Paul_Hossler]

Yes -- but without seeing the rest of the code, it's ONLY A GUESS

Change the Sub definitions to use one cell at a time

Sub ResetFilters (R as range)
...
...
End Sub

[SamT]

I don't understand exactly what you are trying to do. I think you said that you want to run each of those subs on every item in the list every time you run LoopThroughValidationList.

I also think that you really only want to run them for the single item in the list that was selected by the user when they inputted data into some cell.

The Call Method is outmoded and is only kept for backwards compatibility with old macros that use it. However, if you feel that you must use it, you must pass any parameters to the 'Called' Sub in Parentheses like Paul did. This is different from the current practice without the Call method.

Old:
Call ResetFilters (rngCell)
Call ApplyDateFilter (rngCell)
Call ApplyDriverFilter (rngCell)
Call CopyFilteredTable (rngCell)
Call ExportToPDF (rngCell)

Current:
ResetFilters rngCell
ApplyDateFilter rngCell
ApplyDriverFilter rngCell
CopyFilteredTable rngCell
ExportToPDF rngCell

[Paul_Hossler]

The Call Method is outmoded and is only kept for backwards compatibility with old macros that use it.


Sure, ... make fun of us old guys on Thanksgiving :devil2:

[SamT]

'N' a happy Thanksgiving to you, sprite.

I'm old enuff to remember A Bomb drills, B&W TV, and computer programs stored on paper tape.

[opsintern]

Let me try to explain what it is exactly that I'm trying to do.

Basically, I have a sheet with a table in it that has a lot of rows of feedback. This feedback is directed to employees, and I want to be able to send out the feedback to each employee without having to manually export each file and send it. To export a PDF file for each employee, I:



Reset all the filters applied to the table (macro ResetFilters)
Apply a date range filter (macro ApplyDateFilter)
Apply an employee filter (macro ApplyDriverFilter)
Copy the filtered table to another sheet as the first sheet still has other stuff on it that I don't want to print (macro CopyFilteredTable)
Export the sheet to a PDF file


However, I've created another macro just today which answers my for-loop issue by using a table column instead of the data validation list. The thing now is that when Exporting my PDF file, it doesn't listen to the design requirements I give it. I want it to be in landscape mode and to fit all columns on the page, but for some reason it will not listen to the latter. My code for exporting is as follows:



Sub ExportToPDF()


Dim Name As String
Dim Stamp As String
Dim Month As String
Dim WhereTo As String
Dim sFileName As String
Dim FolderLocation As String
Dim Final As String


' Assign Strings
WhereTo = Sheets("Export PDF").Range("C10").Value
Stamp = Sheets("Drivers").Range("E1").Value
If Stamp = "" Then MsgBox ("Make sure a Driver and a Period are selected.")
Month = Sheets("Export PDF").Range("C6").Value
Final = Sheets("Export PDF").Range("C15").Value
' Combine Strings to make the File Name and the FolderLocation
' sFileName = WhereTo & "\" & Month & "\" & Stamp & ".pdf" ' Not using this at the moment, might delete it later
sFileName = Final
FolderLocation = WhereTo & "\" & Month


' Set Page Layout (Pase Setup)
With Sheets("Feedback").PageSetup
Application.PrintCommunication = False
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.1)
.PaperSize = xlPaperA4
.Orientation = xlLandscape
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With

' Create folder in which to store the PDF File if Cell C6 has a value
If Month = "" Then
' Do nothing
Else
If Not IsEmpty(Dir(FolderLocation)) Then
On Error Resume Next
MkDir Path:=FolderLocation
On Error GoTo 0
Else
' Do nothing
End If
End If
Dim D17Value As String
D17Value = Sheets("Export PDF").Range("D17").Value
If D17Value = "Yes" Then
' Export PDF File to folder and showing it
Sheets("Feedback").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName, Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Else
' Export PDF File to folder without showing it
Sheets("Feedback").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName, Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
End Sub


My cell C15 has the final filename in it, a Concatenate function combining all parameters to create a filename in a folder.

Please note that my code might be a bit messy, but I plan on deleting all stuff that shouldn't be there when I'm done with the initial coding.

Thanks for the help so far :)

[opsintern]

Never mind guys, I've figured out what to do.
Apparently I needed to add Application.PrintCommunication = True to the end of my With.PageSetup

Thanks for all the help!