CODEWITHSUNDEEP
vbaexcel
Volkan Yurtseven
Volkan Yurtseven

Reputation: 444

how to check if workbook is closing in deactivate event

SOLVED

Is there any way to check if workbook is closing when the code is in workbook_deactivate procedure? so i can inform a different message to users depending on whether they are just leaving to another workbook or they are closing the file. like following

Private Sub Workbook_Deactivate()

if thisworkbook.closing then
   msgbox "message1"
else
   msgbox "message2"
end if

End Sub

i've searched on the net but no solution at all. so any help would be appreciated

SOLUTION

i've thought of a trick. i'm putting the value 1 in Z1000(if it is available) in before_close event and in deactivate, i'm checking if Z1000's value. that's it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("Z1000").Value = 1 'wherever is avaliable
    Me.Saved = True
End Sub

Private Sub Workbook_Deactivate()
    If Range("Z1000").Value = 1 Then
        MsgBox "quitting"
    Else
        MsgBox "deactivating"
    End If
End Sub

Upvotes: 0

Views: 383

Answers (1)

braX
braX

Reputation: 11755

You can detect that using the BeforeClose Event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ' set Cancel to true to prevent it from closing
End Sub

Upvotes: 1

Related Questions