Can you pause code processing, to go enter some missing details and then resume

[asolanki]

Hi Guys

Maybe a strange one, and possible due to lack of knowledge on my part, but I have the code below written.

Where if it doesn't find a path for a particular sheet 'save path' for the 'save as' function to work - I have the message to the users that states this is missing and needs to be fixed.

Currently it carries on processing records after you dismiss the msgbox, just skipping the records for that sheet( but a message pops up for each row and this could be many rows in each sheet).

What i would like is, that there is a pause in the code - that allows the user to go fix the path in the correct cell and then the code is able to resume from the last record if possible. So it doesn't effect the whole process

Background here - This process I have written - Goes through all the sheets in a WB and saves PDFs to a location specified in the "Dashboard" cells - This can be many records at a time and i don't want it to stop processing due to a missing path, especially it has already done half the sheets in the WB. So what I'm trying to do here is get the user to fix the issue with the path, so that the rest of the sheet gets processed correctly. - if that makes sense

What would you suggest in order for this to be done, please
Thanks in advance, as always
Ash


For Z = 2 To ActiveWorkbook.Sheets("DashBoard").Cells(Rows.Count, 5).End(xlUp).Row

If InStr(ActiveWorkbook.Sheets("DashBoard").Cells(Z, 5), SheetName) Then
newpath = Trim(ActiveWorkbook.Sheets("DashBoard").Cells(Z + 1, 5))

End If
Z = Z + 1
Next
If newpath = "" Then
MsgBox ("FilePath is Missing for the following " & SheetName & " Please Add/Correct the Path")

Else
SaveAsName = newpath & "\" & SheetName & "-" & Split(sht.Cells(i, 2).Value, ",")(0) & "-" _
& Split(sht.Cells(i, 9).Value, ",")(0) & "-" & WeekNum & YearNum & "-" & RefID
Call SaveAsPDF(sht.Cells(i, 8).Value, SaveAsName)
sht.Cells(1, 11).Value = "PDF Location"
sht.Range("K1").Font.Bold = True
Application.Wait (Now + TimeValue("0:00:01"))
sht.Hyperlinks.Add Anchor:=sht.Range("K" & i), _
Address:=SaveAsName & ".pdf", _
TextToDisplay:=WeekNum & YearNum & "-" & RefID
'TextToDisplay:="RowID" & i - ASH Removed to test new Reference
RefID = RefID + 1
newpath = ""

End If
Next
End If

 

[Comfy]

I would suggest that you validate all the paths first before running any additional code.

 

[Comfy]

something like this:

Sub TestForBlanks()
Dim LastRow As Long
Dim BlankRange As Range
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
LastRow = ws.Cells(Rows.Count, 5).End(xlUp).Row


On Error Resume Next
Set BlankRange = Range(Cells(2, 5), Cells(LastRow, 5)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not BlankRange Is Nothing Then MsgBox "missing Paths etc etc": Exit Sub
Next ws
'Continue with Code execution
MsgBox "All Paths Included"


End Sub

 

[asolanki]

Duh!! - Yes - check that everything is there before it actually starts the process
Sorry been a long week and lack of sleep doesnt help - Thanks god its friday!!! - Thanks again Comfy
Ash

 

[Comfy]

Duh!! - Yes - check that everything is there before it actually starts the process
Sorry been a long week and lack of sleep doesnt help - Thanks god its friday!!! - Thanks again Comfy
Ash

You're Welcome and I feel your pain.