Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
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("D ata").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, Structu=True, Windows:=False Application.ScreenUpdating = True 'Restore view DoEvents Application.OnTime Now + TimeValue("00:00:01"), "Restore_view_after_saving" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
On Jul 17, 12:03*pm, ward376 wrote:
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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
On Jul 18, 3:59*am, GS wrote:
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
AP was thinking very hard :
On Jul 18, 3:59*am, GS wrote: 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. Could this possibly be a permissions issue? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
On Jul 19, 1:59*am, GS wrote:
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave
AP formulated the question :
On Jul 19, 1:59*am, GS wrote: 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. Possibly, you need to 'repair' your office installation via ControlPanelAddRemovePrograms! 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? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP!!!! Calling a sub from Workbook_BeforeSave | Excel Discussion (Misc queries) | |||
Workbook_BeforeSave Event | Excel Programming | |||
Workbook_BeforeSave() | Excel Programming | |||
Workbook_BeforeSave | Excel Programming | |||
Workbook_BeforeSave() in xla | Excel Programming |