This possible solution does not require an additional Monitor sheet.
The following code should go into the order sheet's code-module to replace any other Worksheet_Calculate() event handler that might be there.
No need for any Worksheet_Change() event handler at all.
Remove all Timer code and calls to any timer code; no workbook_open event handler calling anything.
Private Sub Worksheet_Calculate()
'there HAS to be a formula on this sheet somewhere that refers to G4 in order for this event handler to be triggered on a change of G4.
Static LastSaveBit
x = Range("G4").Value 'grab the savebit value asap.
If IsEmpty(LastSaveBit) Then LastSaveBit = 0 'initialise LastSaveBit on first run.
If x = 1 And LastSaveBit = 0 Then
LastSaveBit = 1
'Debug.Print "saving " & Now
PDF_Export
' Else
' Debug.Print "NOT saving " & Now
End If
LastSaveBit = x
End Sub
The intention with this code is not to allow a new save to take place without there having been an intervening G4 value of 0.
I've left some commented-out code which are remnants of my testing; you may want to re-enable it for your own testing, otherwise you can delete all comments.
This code requires there to be a formula on the same sheet which refers to cell G4, in order for a change in G4 to trigger the calculate event.
FYI a Static variable is one that retains its value between calls to the sub that contains it.
Your PDF_Export sub should NOT be Private, but start with Sub… and it should be without timer calls, nor any checks on G4 - they've already been done by the time this routine is called:
Sub PDF_Export()
Dim Filename As String, path As String
path = "C:\Users\zachary.revis\Documents\Order Reports\"
With ActiveSheet
Filename = .Range("B1").Text 'I'm assuming B1 is on the order sheet which is the active sheet.
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=path & Filename & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End With
End Sub
or similar.
This last code should really have its range references qualified, eg.:
With Sheets("Order")
or:
With Sheets("Sheet1")
rather than:
With ActiveSheet
And if cell B1 where the filename is held is on a different sheet then use the likes of:
Filename = Sheets("PutAnotherSheet'sNameHere").Range("B1").Text