Workbook_BeforeClose

[Donb209]

I have a finance and budget program using Excel primarily as a method of input and output reports. Most if not all functioning is VBA code. The problem I am having is that when I click the X to exit the workbook I have a message box (my own creation) open to as if I want to save the workbook to the three locations I have programmed into it before I get out, or to just skip it and exit (Yes or No) response. I have found that at my age I occasionally hit the X (exit) button when I mean to use the full-screen button, or just plain am not thinking. As a result, there is no way out of the message box except to exit the workbook. I want to add an abort button to effectively say oooooops, I made a mistake, get out of the close routine. Sort of like the Cancel choice of the Save, Don't Save, Cancel message box you get if you have unsaved changes. I can't make anything work. If I put "Cancel = True" in the close it never goes out of the workbook. I tried to make a conditional statement to establish the Cancel feature but it seems to ignore conditional in the "before close" section. Any suggestions that I can understand without getting too deep into OOP?

 

[rollis13]

Please post your macro Workbook_BeforeClose.

 

[Donb209]

I did get a workaround to work and am posting it below. As shown, it is the code in the "Before_Close module". I am not posting the form, but in way of explanation, it shows a message equal to the "txtin" variable, as shown below. The form has 3 OptionButtons. One titled "YES", one titled "NO" and one titled "ABORT". The second section of the attached code is the CommandBUtton code found in the form. When the YES Option is selected, it backs up the workbook on three different devices. (It is a financial program so good backups are essential.) If NO is selected as when you just open the workbook to look up something or make no changes and just want out. Lastly - if for one reason or another (an old man like me hits the wrong buttons) you can close the form and abort the "CLOSE" function of the workbook. About the same as the system's "CANCEL".
I have implemented this by setting a 'Flag" in a cell on one of the sheets if I wish to abort the closure. Then down in the BeforeClose moduleI I check the cell to see if it is set and react accordingly.
This works fine, but I think I should be able to set a variable in the Form code and test that variable in the close code. I have not been able to pass that variable down to the close routine. It is something I likely just don't know and Google was not able to help as it triggers on the wrong words (the easiest words to find answers for).
SIDE NOTE:
I have no formal training in Excel or VBA. I am self-taught and work primarily from the earliest days of desktop computing and the use of Basic and its predecessors, as a language with a little help from some higher-level languages. I am not employed and am not writing to sell, just for personal use and the enjoyment of creative function (Keeping mind active in senior years). I mention that to explain the older style of programming used. I am trying to pick up some OOP but am not too swift at it, so any help in spite of my coding ignorance will be greatly appreciated.

(1)

[COLOR=rgb(235, 107, 86)]Private Sub Workbook_BeforeClose(cancel As Boolean)


txtin = "Backup this file now?"
Choicesform.TextBox1.Text = txtin
Choicesform.Show

If Sheets("Balance").Cells(30, "H").Value = 1 Then
   cancel = True
End If

fini:
End Sub[/COLOR]

(2)

      [COLOR=rgb(65, 168, 95)]  'This s the "GO" button that executes the form commands

Private Sub CommandButton1_Click()

Sheets("Balance").Cells(30, "H").Value = 0

If Me.OptionButton1.Value = True Then
    Unload Me
    MessagesForm.CommandButton1.Visible = "False"
    Call displayMessage("Backing this workbook on the three primary devices", 0)
    Application.Wait (Now + TimeValue("00:00:1"))
    Call RouterHDbkup ' found in Backupcopiescomplete
    Application.Wait (Now + TimeValue("00:00:1"))
    Call displayMessage("Backing up Router Hard Drive complete! ", 0)
    Call SDBackup
    Application.Wait (Now + TimeValue("00:00:1"))
    Call displayMessage("Backing up SD chip complete! ", 0)
    Call DropboxBU
    Application.Wait (Now + TimeValue("00:00:1"))
    Call displayMessage("Backing up DropBox! ", 0)
    Application.Wait (Now + TimeValue("00:00:3"))
    Call displayMessage("All backups up completed!", 0)
    Application.Wait (Now + TimeValue("00:00:1"))

    MessagesForm.CommandButton1.Visible = "True"
    GoTo getout

ElseIf Me.OptionButton2.Value = True Then
        Unload Me
    GoTo getout
Else
    If Me.OptionButton3.Value = True Then
        Sheets("Balance").Cells(30, "H").Value = 1
        Unload Me
    End If

End If
getout:

End Sub[/COLOR]

 

[rollis13]

Glad you found a workaround since your Workbook_BeforeClose doesn't show much; the dirty work is done by other macros .

 

[Donb209]

After sending the code I am using I dug through some printouts I had from the internet and found one that addressed passing variables from a userForm to a module, I got the idea of moving all code from the userform to a module. I then used a CALL to the macro in the module. The macro set the variable and ended the macro. Execution returned to the userform, which had nothing else in it so it ended and returned execution t the Workbook close routine where the variable could then test it to determine what to do.
Based on that resolution I am closing this, but wanted to put my method here so another viewer might find some help from my method Thanks for jarring my memory and making me reason this out. I'm back on track again. Again - THANKS!