Macro/VBA Question

[drose9]

Hello,

I have a question about a macro I'm using to loop through a combo-box to save a pdf of each result that appears when the combo-box changes value.

This macro works flawlessly for another tab that simply has a different name, everything else is the same. I've checked the spelling/spacing of the names for this macro a dozen times so I'm not sure if the vba used here can't be used more than once per excel file? Any suggestions would be very much appreciated. Thank you.

Sub PDF_Save_ENHANCED_2()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Dim i As Long


'Which cell has data validation
Set dvCell = Worksheets("Scorecard Level 2").Range("B6")
'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)


i = 1
'Begin our loop
Application.ScreenUpdating = False
For Each c In inputRange
dvCell = c.Value


Dim strFilename As String
Dim rngRange As Range




part1 = Worksheets("Scorecard Level 2").Range("B6").Value
part2 = Worksheets("Scorecard Level 2").Range("A21").Value
part3 = Worksheets("Scorecard Level 2").Range("H6").Value




strFilename = "Scorecard Level 2 " & part1 & " " & "(" & part2 & ")" & " " & part3


Sheets("Scorecard Level 2").Select
Sheets("Scorecard Level 2").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\drose\Desktop\lvl 2 Test\" & strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

i = i + 1
Next c
Application.ScreenUpdating = True

MsgBox "All PDFs have been saved."

End Sub




The debugger highlights this line: " Set inputRange = Evaluate(dvCell.Validation.Formula1)"

The error box says: "Run-time error '1004'. Application-defined or object-defined error."



Many thanks,
Drose

 

[JLGWhiz]

Do you have a validation set up in 'Scorecard Level2'!B6?

 

[drose9]

Do you have a validation set up in 'Scorecard Level2'!B6?

Yes, the combo box that you can chose from is linked to B6.

 

[JLGWhiz]

Yes, the combo box that you can chose from is linked to B6.

I think it is balking at the Formula1, but I have never used that method and am not quite sure what it expects when that is used. Here is what Help say about it:

Returns the value or expression associated with the conditional format or data validation. Can be a constant value, a string value, a cell reference, or a formula. Read-only String.

But if you are trying to initialize inputRange as an object variable and the value of your validation does not equate to a rang as recognized by VBA then it would throw an errror.