Infinite loop cancelling BeforeSave

[Greg Lovern]

If I set Cancel = True in the BeforeSave event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True '(to save this change, I set a breakpoint on this
line to skip it; then removed the breakpoint)
End Sub


And then make any change to the workbook, and close the workbook, and
click Yes when prompted whether to save the changes, it prompts me
again whether to save the workbook. As long as I continue clicking
Yes, it continues prompting me in an infinite loop.

Why does Excel continue to prompt? I cancelled the save; shouldn't
Excel just close it without saving?

If the user clicks Yes in that situation, what can I do to stop Excel
from prompting again? An inelegant workaround is to SendKeys a Cancel
key to the prompt dialog, like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SendKeys "Cancel"
Cancel = True
End Sub

But then I'm sending that Cancel key under all Save circumstances. I
could set a flag in BeforeClose and only SendKeys the Cancel key when
that flag is set. But I'd rather do it without SendKeys if possible.

Any ideas?


Thanks,

Greg

 

[Jim Thomlinson]

If you don't want the file to be saved then open it as read only. The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg

 

[Greg Lovern]

If you don't want the file to be saved then open it as read only.

At the time the file is opened, I don't know whether I will want to
cancel the save, and even if I do want to cancel some saves, I might
not want to cancel other saves of the same workbook.

The reason for the cancel is to check
SpecialCells(xlCellTypeConstants), which doesn't work right if run
from the Before_Save event. In the Before_Save event,
SpecialCells(xlCellTypeConstants) reports all cells as occupied. See:

http://groups.google.com/group/micr...79814/5c0cad2e217322da?hl=en#5c0cad2e217322da

So I was cancelling the save, calling another procedure that did the
SpecialCells(xlCellTypeConstants) task, and then setting a flag and
saving from that other procedure. In Before_Save, I checked for that
flag and cancelled if not set. And of course, unset the flag.

Greg

The Cancel
stops the save event but XL does not like closing workbooks that are not
saved. Try adding

ThisWorkbook.Saved = True

That got me further but still wasn't working. I've forgotten exactly
what was going wrong. What I'm doing now is doing the
SpecialCells(xlCellTypeConstants) task in the Before_Close event,
where it works fine, and setting a flag. In Before_Save I check for
that flag, and do the SpecialCells(xlCellTypeConstants) task only if
the flag is not set. And of course, unset the flag. That's been
working fine.

Greg