Before Close Event

[G-Matriix]

Hello All,

I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...

Here it is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
    a = MsgBox("Do you want to Log-Out?", _
        vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True

End Sub

Any Ideas??

Thanks

 

[DonkeyOte]

Not entirely sure what you want to do as the initial code did not make sense (if answer = yes to log out prompt) however below will prompt - if yes then file will save & close
(Note: initial code had savechanges = true -- this will not save, need savechanges:=true to save)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
    a = MsgBox("Do you want to Log-Out?", vbYesNo)
    Select Case a
        Case vbNo
            Cancel = True
        Case Else
            Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges:=True
    End Select
End If
End Sub

 

[G-Matriix]

Not entirely sure what you want to do as the initial code did not make sense (if answer = yes to log out prompt) however below will prompt - if yes then file will save & close
(Note: initial code had savechanges = true -- this will not save, need savechanges:=true to save)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
    a = MsgBox("Do you want to Log-Out?", vbYesNo)
    Select Case a
        Case vbNo
            Cancel = True
        Case Else
            Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges:=True
    End Select
End If
End Sub

Hey thanks,

However, when I close the document it doesn't prompt for the Msgbox...???? When I hit the X to close it should run ......right??????

 

[Norie]

Don't know if it will help but I would suggest you reference the worksheet in the If statement.

If Sheets("OD&D Log-in").Range("H5") = "reconcile" Then

If you don't then VBA will be looking at H5 on whatever it considers the active sheet.

 

[G-Matriix]

Don't know if it will help but I would suggest you reference the worksheet in the If statement.

If Sheets("OD&D Log-in").Range("H5") = "reconcile" Then

If you don't then VBA will be looking at H5 on whatever it considers the active sheet.

Thanks....I don't know what I am doing wrong...it will not prompt....when closed....there is got to be something I am leaving out...

Any Ideas???

 

[Norie]

Where is the code located?

It should be in the ThisWorkbook module.

What result do you get when you open the Immediate Window (CTRL+G) and enter this?

?Application.EnableEvents

If that returns False then it means that at some point, probably by accident, events have been disabled.

To enable them enter this in the Immediate Window.

Application.EnableEvents = True

 

[G-Matriix]

Where is the code located?

It should be in the ThisWorkbook module.

What result do you get when you open the Immediate Window (CTRL+G) and enter this?

?Application.EnableEvents

If that returns False then it means that at some point, probably by accident, events have been disabled.

To enable them enter this in the Immediate Window.

Application.EnableEvents = True

It returned True .....and the code is located in one of the modules

???????? I am puzzled???

 

[Norie]

Which module is the code in?

 

[G-Matriix]

Which module is the code in?

Module 4

 

[Joe_Canadian]

Module 4

Move it to the ThisWorkbook module