ElseIf..nightmare!

[Immatoity]

Hi ..this should work as far as I can see....

All I want to do is bring up a MsgBox if the user exits the file and clicks "No" on save changes, or simply clicks file exit or the "x" at the top of screen

The message is to effectively double warn them that they will have to re-enter data if they don't save..here's the VBA - I keep getting an error "Else without If"....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
       MsgBox "If you don't save, you'll have to recheck all your DD's"
       MsgBox "Do you want to Save Now?", vbYesNo
   If response = vbYes Then ActiveWorkbook.Save
   ElseIf response = vbNo Then MsgBox "You will not save any information on DD's you have entered", vb
End If
End Sub

[Bob Phillips]

Hi ..this should work as far as I can see....

All I want to do is bring up a MsgBox if the user exits the file and clicks "No" on save changes, or simply clicks file exit or the "x" at the top of screen

The message is to effectively double warn them that they will have to re-enter data if they don't save..here's the VBA - I keep getting an error "Else without If"....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
       MsgBox "If you don't save, you'll have to recheck all your DD's"
       MsgBox "Do you want to Save Now?", vbYesNo
   If response = vbYes Then ActiveWorkbook.Save
   ElseIf response = vbNo Then MsgBox "You will not save any information on DD's you have entered", vb
End If
End Sub

If you put the Then action on the same line as the IF, there is no End If required. Use this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "If you don't save, you'll have to recheck all your DD's"
    MsgBox "Do you want to Save Now?", vbYesNo
    If response = vbYes Then
        ActiveWorkbook.Save
    ElseIf response = vbNo Then
        MsgBox "You will not save any information on DD's you have entered", vb
    End If
End Sub

[Immatoity]

as usual xld...thanks

My code doesn't really do what I want though as whether they click yes or no, they still get the option to close without saving??

the attachment shows is the screen they are presented with, whether they click yes or no

[Bob Phillips]

as usual xld...thanks

My code doesn't really do what I want though as whether they click yes or no, they still get the option to close without saving??

the attachment shows is the screen they are presented with, whether they click yes or no

Sorry, didn't spot that.

When you want to test a MsgBox return, you have to use it like a function

ans=MsgBox(....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As Long
    MsgBox "If you don't save, you'll have to recheck all your DD's"
    response = MsgBox("Do you want to Save Now?", vbYesNo)
    If response = vbYes Then
        ActiveWorkbook.Save
    ElseIf response = vbNo Then
        ThisWorkbook.Saved = True
        MsgBox "You will not save any information on DD's you have entered", vbInformation
    End If
End Sub

[Immatoity]

magic....thanks.....you people on here make me look good! I am trying to learn as I go along though

I am ina cheeky mood... I have "nicked" this bit of code from somwhere else...it records the number of saves.... I want to adjust it so it forms a list of username, and time/date of save, adding to the list, not overwriting...not sure how

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("saved").Range("A1").Value = Sheets("saved").Range("A1").Value + 1
End Sub

[Bob Phillips]

magic....thanks.....you people on here make me look good! I am trying to learn as I go along though

I am ina cheeky mood... I have "nicked" this bit of code from somwhere else...it records the number of saves.... I want to adjust it so it forms a list of username, and time/date of save, adding to the list, not overwriting...not sure how

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("saved").Range("A1").Value = Sheets("saved").Range("A1").Value + 1
End Sub

Not tested, just whipped up, so be warned

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iLastRow As Long
    With Worksheets("saved")
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If iLastRow = 1 And .Range("A1").Value = "" Then
            'do nothing
        Else
            iLastRow = iLastRow + 1
        End If
        .Range("A" & iLastRow).Value = Environ("UserName")
        .Range("B" & iLastRow).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
    End With
End Sub

[Immatoity]

Hi ...I am slowly getting there ..and the username thing works a beaut....

I have modified the VBA for the save process but it's falling down...here's the code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim response As Long
    MsgBox "If you don't save, you'll have to recheck all your DD's"
    response = MsgBox("Do you want to Save Now?", vbYesNo)
    If response = vbYes Then
        ActiveWorkbook.Save
    ElseIf response = vbNo Then
        ThisWorkbook.Saved = True
        MsgBox "You will not save any information on DD's you have entered_"
        MsgBox "Are you sure you want to exit without save", vbYesNo, "Last Chance!"
        End If
        If Reponse = vbYes Then
        ActiveWorkbook.Close
    ElseIf response = vbNo Then
        ActiveWorkbook.Save
End If

It all works perefectly apart from what happens at the end when the "last chance" MsgBox appears....regardless of whether they click Yes or No, it saves the file and closes it!

[Norie]

Well you don't actually seem to be using the 'last chance' message box to assign a value to Response.

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
	Dim response As Long 
	MsgBox "If you don't save, you'll have to recheck all your DD's" 
	response = MsgBox("Do you want to Save Now?", vbYesNo) 
	If response = vbYes Then 
		ActiveWorkbook.Save 
	Else 
		ThisWorkbook.Saved = True 
		MsgBox "You will not save any information on DD's you have entered_" 
		Response=MsgBox "Are you sure you want to exit without save", vbYesNo, "Last Chance!" 
	End If 
	If Reponse = vbYes Then 
		ActiveWorkbook.Close 
	Else 
		ActiveWorkbook.Save 
	End If

By the way you don't need ElseIf as Response can only be vbYes or vbNo.

[Immatoity]

hi...still not working...this is the code I have now...and it still saves it if they click no on the "last chance" box..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim response As Long
    MsgBox "If you don't save, you'll have to recheck all your DD's"
    response = MsgBox("Do you want to Save Now?", vbYesNo)
    If response = vbYes Then
        ActiveWorkbook.Save
    Else
        ThisWorkbook.Saved = True
        MsgBox "You will not save any information on DD's you have entered_"
        response = MsgBox("Are you sure you want to exit without save", vbYesNo, "Last Chance!")
    End If
    If Reponse = vbYes Then
        ActiveWorkbook.Close
    Else
        ActiveWorkbook.Save
    End If
End Sub

[Bob Phillips]

hi...still not working...this is the code I have now...and it still saves it if they click no on the "last chance" box..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim response As Long
    MsgBox "If you don't save, you'll have to recheck all your DD's"
    response = MsgBox("Do you want to Save Now?", vbYesNo)
    If response = vbYes Then
        ActiveWorkbook.Save
    Else
        ThisWorkbook.Saved = True
        MsgBox "You will not save any information on DD's you have entered_"
        response = MsgBox("Are you sure you want to exit without save", vbYesNo, "Last Chance!")
    End If
    If Reponse = vbYes Then
        ActiveWorkbook.Close
    Else
        ActiveWorkbook.Save
    End If
End Sub

Typo alert!!!!

If you used option Explicit you wouldn't get this problem.

If Reponse = vbYes Then
        ActiveWorkbook.Close
    Else
        ActiveWorkbook.Save
    End If

should be

If response = vbYes Then
        ActiveWorkbook.Close
    Else
        ActiveWorkbook.Save
    End If

[Immatoity]

oops....sorry!

cheers it works ...sort ofBUT.....

If they click "no" or "yes" in the last chance box... they have to repeat all the options again before it does what they want....pretty annoying for the end user

[Bob Phillips]

If they click "no" or "yes" in the last chance box... they have to repeat all the options again before it does what they want....pretty annoying for the end user

Can you re-phrase that as I don't understand. If they click yes or no it does something then exits.

[Immatoity]

Hi

What happens, in this order is

1) If they click file close or exit, the msgbox "If you don't save..." appears.

2)They can only click ok here -then msgbox "Do you want to Save Now..Yes/No" appears... if they click yes it saves the file and msgbox "If you don't save (in (1) above appears again and then the "Do you want to save" box reappears..they can click yes and it closes, but they have had to do it twice!
If they click no when this box first appears the msgbox "You will not save any....." appears with the response OK only available...they click that and the "Last chance" msgbox appears... if they click no on this one, the box "If you dont save reappears", then "Do you want to save" again, and if they click no, a couple more appear

In other words.....

If they do not want to save they have to click "no" on 2 seperate occasions in the 2 seperate msgboxes...same if they do want to save..they have to click yes twice too...

I just want it to close without saving if they click no in the "last chance box" and to save and close it if they click yes..

Hope this makes sense...maybe open a blank file and try the code yourself...might make it easier?

[johnske]

I just want it to close without saving if they click no in the "last chance box" and to save and close it if they click yes..

Surely you mean the opposite here? ... i.e. To close without saving if they click yes to "Are you sure you want to exit without save"



Try this variation

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As Long
MsgBox "If you don't save, you'll have to recheck all your DD's"
response = MsgBox("Do you want to Save Now?", vbYesNo)
If response = vbYes Then
ActiveWorkbook.Save
Else
ThisWorkbook.Saved = True
MsgBox "You will not save any information on DD's you have entered_"
response = MsgBox("Are you sure you want to exit without save", vbYesNo, "Last Chance!")
If response = vbNo Then
ActiveWorkbook.Save
End If
End If
End Sub

[Immatoity]

solved...cheers ( and sorry for delay in replying!)

thanks for all your help