Workbook_BeforeSave

[AP]

I have a "Workbook_BeforeSave" subroutine (below) that works perfectly
in most PC's, but in my workplace, it only works selectively (only
small bits of the code within the subroutine work). The only bit that
triggers is the "MsgBox" line - everything else is ignored.

Could this be a security setting in my workplace PC's ? (since the
macro works fine on other PC's)

Thanks for your help.

---


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

Application.EnableCancelKey = xlDisabled

'Move cursor to default position
Application.ScreenUpdating = False
Sheets("Data").Select
admin_change = True
Cells(1, 100).Select
admin_change = False
ActiveWindow.ScrollRow = 1: ActiveWindow.ScrollColumn = 1
Display_all 'A subroutine that unhides any hidden cells

'Ensure all fields in "Input" sheet have been duly populated
r = 4
While Sheets("Data").Cells(r, 11).Value <> ""
If
Application.WorksheetFunction.CountBlank(Sheets("Data").Range("B" & r
& ":F" & r)) > 0 Then
Cancel = True
Application.ScreenUpdating = True
admin_change = True
Sheets("Data").Range(Cells(r, 1), Cells(r, 11)).Select
'Sheets("Input").Cells(r, 1).EntireRow.Select
admin_change = False
MsgBox "The highlighted record is incomplete." & Chr(13) &
Chr(13) & "Please complete the record thoroughly before saving this
file.", 16, "MIS Incomplete Record"
Exit Sub
End If
r = r + 1
Wend

'Hide sheets
ActiveWorkbook.Unprotect Password:=pro
Sheets("Notice").Visible = True
Sheets("Data").Visible = False
Sheets("Print_buffer").Visible = False
Sheets("Email_buffer").Visible = False
Sheets("Buffer").Visible = False
Sheets("Options").Visible = False
Sheets("Notice").Select
ActiveWorkbook.Protect Password:=pro, Structure:=True,
Windows:=False
Application.ScreenUpdating = True

'Restore view
DoEvents
Application.OnTime Now + TimeValue("00:00:01"),
"Restore_view_after_saving"

End Sub

 

[ward376]

Wish I could help but all I can do is corroborate - I experience
similar things at work.

No new OS's (still on XP), Office versions or other software (that I
can detect). It started in Office 2007 approximately mid-April 2011,
and that is after using 2007 for more than a year on the same machine.
Doesn't matter whether I'm on the network or completely isolated, have
a single file open or a dozen apps and files open.

Tools that have worked perfectly for years by multiple employees have
begun 'acting up' or stopped working completely due to:
1. Lines of code that are skipped or only partially executed before
going to the next line.
2. Functions that have ceased to be recognized, i.e.
(Environ("blah"))? "I don't know wtf you're talking about, here's an
error!" (This really throws a monkey wrench into automated file and
folder control for multiple users in a corporate setting.)
Various other errors, but these two are the ones that have cost me the
most time, energy and focus.

I have worked on some of these files with other employees around the
country with similar results. I have tried on every company machine I
could get my hands on, none acted better and most acted worse. In each
case, the code executes perfectly EVERY TIME on my six-year old home
machine (XP/2007) with half the processing capacity and 1/3 the memory
of my company machine. Upgraded to Office 2010 at work after first
experiencing these things and results are the same.

Seems like a resource allocation thing - like the machine doesn't want
to execute a line, or decides part-way through it doesn't want to
finish executing a line and just goes on to the next line and repeats
it's finicky little act. I cannot consistently reproduce, and it's not
always the same things that break.

Any IT folks out there care to share or speculate? Restarting apps/
machines does not help. Hard drive space plentiful. Temp files
regularly cleaned. Defrag occasionally. Issues occur in the same
projects whether in fresh files or 10-year old files. Most of the
tools are considerably less than 100kb, the absolute largest is less
than 6mb fully loaded. Did I miss anything obvious?

 

[AP]

Wish I could help but all I can do is corroborate - I experience
similar things at work.

Thanks for sharing. Sounds like you and I might be experiencing a
similar problem. So far, what I've noticed is that the "ignored" code
tends to be related to file-saving e.g. Workbook_BeforeSave.

Also, a simple line such as "ActiveWorkbook.Close SaveChanges:= False"
would fail to execute. The workbook would remain open. Again, the same
code in the same workbook would work perfectly on other PC's. Only in
the PC's in my workplace would the code be silently ignored.

Someone told me it could be that DotNet Framework hasn't been updated
probably but it appears to be updated on my workplace PC.

Anybody IT gurus out there who might have an idea on this ?

 

[GS]

Usually, this type of behavior happens when the workbook is corrupt.
Try rebuilding the project in a new workbook and see if that resolves
the issues!

 

[AP]

Usually, this type of behavior happens when the workbook is corrupt.
Try rebuilding the project in a new workbook and see if that resolves
the issues!

Thanks. Tried it but unfortunately it didn't work. MS Excel mnight
need to be re-installed ?

I notice all code relating to file-saving tend to fail. It's a huge
handicap.

 

[GS]

AP was thinking very hard :

Thanks. Tried it but unfortunately it didn't work. MS Excel mnight
need to be re-installed ?

I notice all code relating to file-saving tend to fail. It's a huge
handicap.

Could this possibly be a permissions issue?

 

[AP]

Could this possibly be a permissions issue?

The strange thing is that manual saving works (File > Save) but any
code (except MsgBox) associated with the save event are ignored. And
no amount of pausing or DoEvents remedies it.

Even the "SaveChanges" part of the following gets ignored:

ActiveWorkbook.Close SaveChanges:=False

I have to get around this with:

ActiveWorkbook.Saved = True
ActiveWorkbook.Close

 

[GS]

AP formulated the question :

The strange thing is that manual saving works (File > Save) but any
code (except MsgBox) associated with the save event are ignored. And
no amount of pausing or DoEvents remedies it.

Possibly, you need to 'repair' your office installation via
ControlPanel>AddRemovePrograms!

Even the "SaveChanges" part of the following gets ignored:

ActiveWorkbook.Close SaveChanges:=False

I have to get around this with:

ActiveWorkbook.Saved = True
ActiveWorkbook.Close

What is 'admin_change' doing, and why are you using it?

 

[AP]

Possibly, you need to 'repair' your office installation via
ControlPanel>AddRemovePrograms!

IT would need to roll out the re-install to all company PC's. Not
impossible but there'll lots of inertia to fight.

What is  'admin_change'  doing, and why are you using it?

Nothing that would affect the execution of the BeforeSave routine.
This is very old code that has worked for years in many other
environments (but the current one). I used this method of disabling
other events (such as SelectionChange) in the days before I discovered
Application.EnableEvents i.e. if admin_change is true then the
SelectionChange code will not execute.