prompt save only when changes have been made

[LFKim2018]

whenever I close a workbook, even though no changes have been made, a message box prompt to save or not almost always appear.

I want the workbook to prompt only when there are changes made - and close right away when there are not (no prompt nor message box)..

thank you

 

[LFKim2018]

Please check PM once more.
I've sent you email as well.
But please do not post your email here as it is sensitive data.

Mr ZVI
I got it. I send you the file..thank you

 

[Joe4]

We ask that if you do solve the issue that you please post it back to this thread here so you are in compliance with rule 4 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thank you

 

[ZVI]

The conclusions:

1. Me.Saved should be the bottom code line of Workbook_Open().
2. StartBlink() subroutine periodically changes interior color of the cell Form!G32, StopBlink() do the same once, and Excel treats it as workbook changing. That is why the saving dialog is appeared. Solution can be in storing of Thisworkbook.Saved status before changing cell's color and restoring that status after the changing.

I've sent you back the fixed workbook with code changed in those subroutines:
ThisWorkbook.Workbook_Open
ThisWorkbook.Workbook_BeforeClose
Module5.StartBlink
Module5.StopBlink
Sheet20.Worksheet_Change

The mentioned code looks like the follows:

' Code of ThisWorkbook module
Private Sub Workbook_Open()
  ' Your code is here
  '...
  Me.Saved = True
End Sub
 
Private Sub Workbook_BeforeClose(CANCEL As Boolean)
  ' Your code is here
  '...
  StopBlink
End Sub

' Code of Module5
Public rRange As Range  ' Sheet20.Range("G32")
Dim dNextTime As Double
 
Sub StartBlink()
  Dim IsSaved As Boolean
  On Error GoTo ErrorHandle
  IsSaved = ThisWorkbook.Saved  ' Save status
  With rRange.Interior
    If .ColorIndex = 15 Then
      .ColorIndex = xlNone
    Else
      .ColorIndex = 15
    End If
  End With
  ThisWorkbook.Saved = IsSaved  'Restore status
  dNextTime = Now + TimeSerial(0, 0, 1)
  Application.OnTime dNextTime, "StartBlink", , True
  Exit Sub
ErrorHandle:
  MsgBox Err.Description & " Procedure StartBlink."
  Set rRange = Nothing
End Sub
 
Sub StopBlink()
  Dim IsSaved As Boolean
  IsSaved = ThisWorkbook.Saved  ' Save status
  On Error Resume Next
  Application.OnTime dNextTime, "StartBlink", , False
  rRange.Interior.ColorIndex = xlNone
  Set rRange = Nothing
  ThisWorkbook.Saved = IsSaved  ' Restore status
End Sub

' Code of Sheet20 - Sheets("Form")
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim IsSaved As Boolean
  IsSaved = ThisWorkbook.Saved  ' Save status
 
  ' Your code is here:
  Sheets("E-L1").Visible = ([C3] > 0 And [B3] > 0 And [B1] > 0)
  ' ... and so on ..
 
 
  ThisWorkbook.Saved = IsSaved  ' Restore status
 
End Sub