After SaveAs Event

[ZipCurs]

Hello,

I have a VBA application that appears to work fine. My only known issue is
that when I perform a manual Save As, I get a bunch of garbage on the active
window. I have had this problem and the exact same garbage when running
individual routines in the application, and have gotten rid of them with
Application.ScreenUpdating=False. Minimally, my problem is that I have no
clue where to put this after a manual SaveAs.

In ThisWorkbook, I use Workbook_Open, Workbook_Deactivate,
Workbook_Activate, and Workbook_SheetActivate. None of these appear to run
during Save As operations.

Thank you for you help in advance.

 

[Bob Umlas]

you want the Workbook_beforesave event

 

[Ryan H]

Put it in this event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Hope this helps! If so, let me know, click "YES" below.

 

[Ryan H]

Use this event. Hope this helps! If so, let me know, click "YES" below.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False

' your code here

Application.ScreenUpdating = True

End Sub

 

[ZipCurs]

Bob & Ryan,

I tried this before and I tried it again. It does not work. I even put a
"stop" in it to ensure that it is accessed, and it is. The problem is that
the garbage shows up after the BeforeSave. I even tried it with
ScreenUpdating left False.

I could imagine doing an automatic SaveAs in BeforeSave and then cancelling
the manual one but these seems a bit hardcore. Any other thoughts?

 

[ZipCurs]

Bob,

Thank you for replying. Please see my response to Ryan H.

 

[Ryan H]

This is a stretch but it might work. Read the help section on DoEvents.
Maybe your operating system has a lot going on and you need to let it finish
before VBA continues or maybe a weak video card. I'm just guessing. Hope it
helps! If so, let me know, click "YES" below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False

' your code here
DoEvents
' your save code here

' the rest of your code here
DoEvents
Application.ScreenUpdating = True

End Sub

 

[ZipCurs]

Ryan,

You have answered my question, although I don't plan to act on it. I will
live with a little cosmetic garbage after SaveAs. The primary issue is "your
save code here". I am not really excited about getting into having the right
version.

Thanks

 

[Jacob Skaria]

Try disabling the alerts...as below

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' your code here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[ZipCurs]

Hello Jacob,

Thanks for your input. Your recommendation would work if I were willing to
insert SaveAs code in the "your code here" area, and Cancelling the manual
action. I have tried this and I don't want to deal with getting the versions
right, dealing with repeat names, etc. All I want to do is a simple action
after the SaveAs. Any thoughts would be welcome, but I think that I am done.