"Save" macro problem, still prompted to save when closing workbook (?)

[StargateFanFromWork]

I got this code from the archives to save the workbook: ThisWorkbook.Save

When I'd recorded saving the workbook earlier, the code was very similar,
ActiveWorkbook.Save.

The difficulty is that after using either macro, yes, I do see the saving
message at the bottom of the workbook that alerts me to the saving process
but when I immediately go to close the workbook, I'm still prompted to save
it. Yet when I click on FILE > SAVE, I close the workbook and the prompt
doesn't come up.

Is there better code to use rather than either workbook.save that will
really save the file?

TIA.

 

[Guest]

Activeworkbook.Save

saves the activeworkbook

thisworkbook.Save saves the workbook that contains the code regardless of
which workbook is active.

Perhaps you aren't saving the workbook you think you are saving.

 

[Guest]

Here is a little background into saving. Excel uses a flag to indicate
whether the file needs to be saved or not. When you Save the workbook it sets
the flag to true indicating that a save is not necessary. Thisworkbook.Save
will set that flag to true. If your macro then goes on to do anything to the
wrokbook then the flag will be set back to false and you will be prompted to
save the workbook. If you want to you can manually set the flag using:

ThisWorkbook.Saved = True 'No save required
or
ThisWorkbook.Saved = False 'Prompted to save

 

[StargateFanFromWork]

Okay. Then what will work?

I've only ever had the one workbook open when I'm doing this, which is what
is odd. When I record FILE > SAVE for that one open workbook, I get the
Activeworkbook save code. But when I use that code and go to close the
file, I get the prompt. Yet those very same keystrokes done manually allow
me to close the workbook without a prompt. What's the solution?

Tx.

 

[StargateFanFromWork]

But the macro doesn't go on to do anything else. I only have that one line
of code to save the workbook in that macro and that's it. That file is the
only one that was open when I pressed the save button on my commandbar and
then I've gone on to close right away only to get the prompt. But I just
saved the file! <g> It's weird as I guess I don't know what Excel is doing
in the background that is different in each case since the two actions
supposedly should accomplish the same thing.

I don't understand re the code below. I'm not sure it would resolve this
issue, would it? I just need to save the workbook properly so that when I
then go to close the file, I don't get a prompt. This is just so weird.
I'd just like to get to the bottom of this and get this to work, but if
worse comes to worst, I'll just remove the bottom. But now I would like to
understand before going that route.

Tx. D

 

[Guest]

if your closing the workbook containing code by using code code

Thisworkbook.Save
Thisworkbook.Close SaveChanges:=False

if your closing the activeworkbook workbook

ActiveWorkbook.Save
ActiveWorkbook.Close SaveChanges:=False

if you want to risk losing changes, then try use Jim's suggestion in the
BeforeClose event.

 

[Guest]

Do you have any event code that might be firing after the save? If so that
code could be modifying the book and then you will be prompted to save the
changes. something is modifying the book after the save...

 

[StargateFanFromWork]

You know, Excel can really make me feel stupid (no comments pls <lol>). At
first glance the first code below looked like it would work, and it does,
except it closes the file after saving. <g> One step forward, one step
back. I'm beginning to believe that this might not be do-able. See, it's
only to save wrist movement. I have a long commandbar and I had an empty
button on it because I'd separated a group of icons from another group with
a space rather than just a line. So I added the SAVE macro there because it
saves moving the mouse out of the comfortable range of the commandbar.
Oftentimes, yes, I'll close the workbook right after, but not always. And
it's a reflex to periodically save anyway while I'm working on a file.
Since I'm using the workbook's commandbar that I set up for this file a lot,
it just seemed easier to put a save there to save having to move all the way
up to the default toolbar.

With this new code I was able to go back to the archives to search again but
I haven't found the False flag without the .Close. Is that truly the case?
There's no way to do a save and a True flag so that when I do close the
file, if there haven't been any changes I won't be prompted?

Thanks. D

 

[StargateFanFromWork]

Oh, for heaven's sake! <lol> It's one little line of code right after the
save that I never think about. I have a tendency to just copy/paste that
into every macro ina workbook of this type that I just never think of it.
It's the protect code to re-protect the sheet. But I think in the case of
this one macro, I can get rid of that as on thinking it through, it's not
necessary to have.


The original code:
Sub SaveWorksheet()
ActiveSheet.Unprotect 'place at the beginning of the code
ThisWorkbook.Save
ActiveSheet.Protect ' place at end of code
End Sub

Now I just have the one line:
Sub SaveWorksheet()
ThisWorkbook.Save
End Sub

Thanks. You guys have so much patience. To think that something so simple
was tripping me up. <g>

Cheers. D