Auto_close

[Francis Hookham]

Auto_close

I need to choose between saving changes to a spreadsheet or not before
closing

If I save the changes then I need to record the date on the sheet

This is the code written so far - do please refine it but, most importantly,
please show me how to bypass the "Do you want to save changes" alert which
appears when I do not want to save them
(I am using this code I This Worksheet rather than in the Personal Macro
Workbook)
Many thanks
Francis Hookham

Sub auto_close()
WasSpreadsheetAltered
End Sub

Sub WasSpreadsheetAltered()
Response = MsgBox("Has this sheet been changed?" & vbNewLine & vbNewLine
& "If so do you want to save the changes?", 260, "Changes to this sheet")
If Response = vbYes Then
Application.Goto Reference:="DateLastAltered"
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.Goto Reference:="R1C1"
End If
ActiveWorkbook.Close
End Sub

 

[Bernard Rey]

Basically, the answer to your question would be to use an
instruction like : " ThisWorkbook.Saved = True" or,
depending upon circumstances, "Application.DisplayAlerts =
False".

In your case, I'd suggest you change your "Auto_close"
macros (considered as "old-fashioned") for a
handy "BeforeClose" event procedure

Paste these lines in the "ThisWorkbook" codesheet (not in
a "Module" codesheet) of the Workbook you want to control:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Response = MsgBox("Has this sheet been changed?" & _
vbNewLine & vbNewLine & "If so do you want to save" _
& " the changes?", 260, "Changes to this sheet")
If Response = vbNo Then
ThisWorkbook.Saved = True
Exit Sub
Else
Range("DateLastAltered") = Now
ActiveWorkbook.Save
End If
End Sub

This macro will run before closing the workbook (no too
much of a surprise) and does (should do) what you expect.
Note that when the answer is "vbNo", the Workbook is now
considered as already saved, so it won't display the
annoying message, and then jumps out of the macro.

 

[J.E. McGimpsey]

Auto_close

I need to choose between saving changes to a spreadsheet or not before
closing

If I save the changes then I need to record the date on the sheet

This is the code written so far - do please refine it but, most importantly,
please show me how to bypass the "Do you want to save changes" alert which
appears when I do not want to save them
(I am using this code I This Worksheet rather than in the Personal Macro
Workbook)
Many thanks
Francis Hookham

I'd consider a slightly different approach. XL has Events that are
triggered by such things as opening or closing a workbook, making
changes to the workbook, saving the workbook, etc., that can then
automatically fire a macro.

You can use the Workbook_BeforeClose event to fire this macro. I'd
suggest also giving the user a chance to cancel out of the save - if
you're prompting him or her to think about what they've changed,
they may decide they need to check or to modify something. Put this
in the ThisWorkbook code module rather than a regular code module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As Integer
response = MsgBox( _
"Do you want to save any changes to this workbook?", _
vbYesNoCancel + vbDefaultButton2, "Changes to workbook")
If response = vbYes Then
Range("DateLastAltered") = Date
Me.Save
ElseIf response = vbCancel Then
Cancel = True
Else
Me.Saved = True
End If
End Sub


I'd also consider simply automating the update using the BeforeSave
event. This also should go in the ThisWorkbook code module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Range("DateLastAltered").Value = Date
End Sub

The potential downside to this is that since it updates before the
save, if the user cancels the save, the date has already been
changed.

 

[TH]

I have a workbook where I set calculation to manual (with good reason I
believe). This causes problems with other open workbooks as they are set to
manual as well. (I think this is poor design in that this changes all open
workbooks to manual - each should have it's own calc setting.)

Anyhow, I used the worksheet deactivate event to turn calculation back to
auto so if the user changes to another workbook or closes this one they're
back to 'normal'. The PROBLEM is this CANCELS any COPY the user may have for
copying cells to another workbook or where ever.

How can I get calculation back to auto without canceling the COPY?

Terry

 

[J.E. McGimpsey]

I have a workbook where I set calculation to manual (with good reason I
believe). This causes problems with other open workbooks as they are set to
manual as well. (I think this is poor design in that this changes all open
workbooks to manual - each should have it's own calc setting.)

Anyhow, I used the worksheet deactivate event to turn calculation back to
auto so if the user changes to another workbook or closes this one they're
back to 'normal'. The PROBLEM is this CANCELS any COPY the user may have for
copying cells to another workbook or where ever.

How can I get calculation back to auto without canceling the COPY?

I can't reproduce this in XLv.X - the Mac clipboard is persistent
even if the "marching ants" are turned off. I used the
Workbook_Activate and Workbook_Deactivate events to check. Clearing
the clipboard on a Mac is sometimes difficult - for information on
how to do it, check out

http://www.mcgimpsey.com/excel/clearclipboard.html

 

[Francis Hookham]

Many thanks, J E McGimpsey and Bernard Rey - self taught, mostly by trial
and error (mostly error), from the earliest days of Multiplan, I am an
amateur but enthusiastic XL user as earleir questions have shown - all my
macros to date have been written within Module modules. Now you introduce me
to macros in the Workbook codesheet

1
Is it possible for you give me (or guide me to) an explanation of macros in
codesheets of Modules, the Workbook or I imagine a specific Worksheet and
rule of thumb about which type of macro to place where?

2
Bernard Rey wrote 'In your case, I'd suggest you change your "Auto_close"
macros (considered as "old-fashioned") for a handy "BeforeClose" event
procedure ' which is interesting but difficult for me to see the
difference

3
Private, Public or not is also an area I am uncertain about - any guidance
please

4
All my Personal Macro Workbook macros are in Module codesheet - should I be
putting these macros in the Workbook codesheet

Surely guidance on these points will be of use to others in my shoes

Thanks

Francis Hookham

 

[Francis Hookham]

Many thanks, J E McGimpsey and Bernard Rey - self taught, mostly by trial
and error (mostly error), from the earliest days of Multiplan, I am an
amateur but enthusiastic XL user - all macros to date have been within
modules. Now you introduce me to macros in the Workbook codesheet rather
than the Module codesheet

1
Is it possible for you give me (or guide me to) an explanation of macros in
Modules, the Workbook or I imagine a specific Worksheet and rule of thumb
about which type of macro to place where?

2
Bernard Rey wrote 'In your case, I'd suggest you change your "Auto_close"
macros (considered as "old-fashioned") for a handy "BeforeClose" event
procedure ' which is interesting but difficult for me to see the
difference

3
Private, Public or not is also an area I am uncertain about - any guidance
please

4
All my Personal Macro Workbook macros are in Module codesheet - should I be
putting these macros in the Workbook codesheet

Surely guidance on these points will be of use to others in my shoes

Thanks

Francis Hookham

 

[J.E. McGimpsey]

Many thanks, J E McGimpsey and Bernard Rey - self taught, mostly by trial
and error (mostly error), from the earliest days of Multiplan, I am an
amateur but enthusiastic XL user as earleir questions have shown - all my
macros to date have been written within Module modules. Now you introduce me
to macros in the Workbook codesheet

1
Is it possible for you give me (or guide me to) an explanation of macros in
codesheets of Modules, the Workbook or I imagine a specific Worksheet and
rule of thumb about which type of macro to place where?

Worksheet and Workbook code modules are specific types of Class
modules. Clas modules, unlike the regular modules you've been using,
can receive Event notifications from XL for certain events, such as
SelectionChange, BeforePrint, Activate, etc. For a list of which
events are recognized, open the module, set the left-hand dropdown
to Workbook/Worksheet, and look at the choices in the right-hand
dropdown.

If there is an event-handler (i.e., event macro) defined for that
event, it is run automatically. For instance, if the worksheet
module contains a Worksheet_Change() event macro, then every time
the user makes an entry (or a remote source changes the entry), that
macro will be run. Some of these macros have built-in arguments,
such as

Private Sub Worksheet_Change(byRef Target As Excel.Range)

where Target is the range variable that refers to the changed cell.

Another difference between Workbook/Worksheet and regular modules is
that the object reference "Me" is defined, which refers to the
object (Workbook or Worksheet). So instead of

ThisWorkbook.Close

that you would use in a regular module, in the ThisWorkbook module
you could use

Me.Close

instead.

Another key difference is the way that unqualified references are
handled. In a regular module

Range("A1")

refers to cell A1 on the ActiveSheet. In a Worksheet module, that
unqualified reference refers to that worksheet.

For that reason, placing general macros in the Thisworkbook or
worksheet code modules can cause some unexpected behavior. I
strongly recommend placing only event macros in the ThisWorkbook and
Worksheet code modules, and placing other macros in regular code
modules. You can also place all your macros in regular code modules
and call them from your event macros. Event macros, obviously, have
to be in their respective class modules.

For more, see http://www.cpearson.com/excel/events.htm

2
Bernard Rey wrote 'In your case, I'd suggest you change your "Auto_close"
macros (considered as "old-fashioned") for a handy "BeforeClose" event
procedure ' which is interesting but difficult for me to see the
difference

Couple of differences: Automacros will not run automatically if a
workbook is opened from code - you need to use the RunAutoMacros
method.

Automacros are also only supported for compatibility with
pre-XL97/98 versions. That doesn't mean they're going away anytime
soon (XL4 macros still run, after all), but there's no guarantee.

Also, the BeforeClose event has a Cancel argument which allows you
to cancel the closing of the book if your conditions aren't met.

3
Private, Public or not is also an area I am uncertain about - any guidance
please

Macros declared Private are only accessible within the code module
where the macro resides. Event macros are by default private, since
it doesn't make sense to call them from outside - they're triggered
by events. Public macros are accessible from anywhere in the
project, and will show up in the Run Macro dialog. If I have a macro
that uses several submacros, I often make the macro public and the
submacros private so that the user doesn't see the submacros.

4
All my Personal Macro Workbook macros are in Module codesheet - should I be
putting these macros in the Workbook codesheet

Almost certainly not - I'd recommend keeping them in regular code
module(s).

In my Personal Macro Workbook, I have a Workbook_Open event macro
that calls a series of other macros in regular code modules which
load add-ins, replace toolbars and menus, reconfigure the keyboard,
initiate a class object to automatically remove hyperlinks, and set
certain preferences. This is because I like a highly customized work
environment. I also have a Before_Close event macro that undoes most
of those things. Most users don't need or want to do those things.

I also have a boatload of macros in about 5 different regular code
modules, organized by function or purpose. These are the ones that
are called from my keyboard shortcuts or toolbar buttons, though I
put most of those routines in add-ins.