On Mar 14, 1:43*pm, "Peter T" <peter_t@discussions wrote:
What do you mean by "it seems to skip 3-lines of code". It should be very
easy to determine if they are working or not with some simple debug lines,
eg

debug.print activesheet.name
ShtS.Activate
debug.print activesheet.name, ShtS.name, ShtS.ProtectContents
Call shUnprotect
debug.print activesheet.name, ShtS.name, ShtS.ProtectContents

' in the first line of shUnprotect
debug.print "shUnprotect"

Looks like your sheet is not getting unprotected for some reason

Lines of code don't suddenly get skipped unless, just conceivably, there is
some severe corruption in the project. To eliminate that possibility clean
the project with Rob Bovey's CodeCleaner addinhttp://www.appspro.com/Utilities/CodeCleaner.htm

Regards,
Peter T

"Canlink" wrote in message

...
On Mar 14, 7:37 am, "Peter T" <peter_t@discussions wrote:





64k as a limit has been reported by some but I'm not sure there's any
evidence that such a defined limit exists. I have (unwisely) had much more
than that in one module without problems. If there is a limit it might be
due to other factors, eg lines of actual code, number of procedures,
callers, variables, very difficult to pin point.

Obviously from a design point of view it's bad practice to include that
much
in one module, but that's a different matter. However modern systems can
include several mg, or +100k lines of code (exclusive of white space &
comments).

As to why your code is suddenly not working it must surely be because
something somewhere has changed. Try and explain what you mean by "doesn't
work"

Regards,
Peter T

"Canlink" wrote in message

....
Is there a limit on how much code you can place in a VBA file?
All works well except the macro I call "VacUsed"
It is called from a couple of procedures I post the last
procedure "ThisWorkBook" use to close and save the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call FilterTestOff
Call VacUsed
Call DeleteMenu
Call AllProtect
Sheets("VacationAccrued").Activate
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As Boolean)
Call AllProtect
Sheets("VacationAccrued").Activate
End Sub

And this is the VacUsed Procedu
Sub VacUsed()
'
' VacUsed Macro
' Macro recorded 5/16/2008 by Geoffrey Feldman
'
' Stores "Vacation Days Taken" from Vacation Accured Sheet
'
Set Wkb = ActiveWorkbook
Set ShtA = Wkb.Worksheets("VacationAccrued")
inLRw = ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row
Set ShtS = Wkb.Worksheets("VacUsedStorage")
ShtS.Activate
Call shUnprotect
ShtS.Range("B2:C1000").ClearContents
' Update VacUsed Names from VacAccrue
ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B"
& inLRw).Value
' Update VacUsed Days Taken from VacAccrue
ShtS.Range("C2:C" & inLRw - 1).Value = ShtA.Range("I3:I"
& inLRw).Value
ShtS.Columns("B:C").EntireColumn.AutoFit
Range("B2").Select
Application.CutCopyMode = False
Call shProtect
ShtA.Activate
Range("B3").Select
End Sub
The macro skips the call "shUnProtect" which is needed to continue
the
update process

Your expert help would be appreciated

OK I will explain what happens:
You run the procedure that I call "VacUsed" directly and it works
fine, However when I call the same procedure from another routine like
my closing routine I can no longer rely upon it! It seems to skip 3-
lines of code "ShtS.Activate: "Call shUnprotect" and
"ShtS.Range("B2:C1000").ClearContents" *it is as if they are
considered info lines and not action lines. It then stops at the line
" ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" & inLRw).Value" with an error that states you have not unprotected the
worksheet.

This is extremely confusing to me and is not good for my client
relations either.

Thanks for the cleanup tip, I will send the project back to the client
and we will see if it works tomorrow. I was making changes to other
modules and to another procedure in the "contaminated" module but I
did not touch the procedure "VacUsed". I also added the Debug.Print
suggestion so that I can go on-line with my client and watch again
what happens. All seemed to work once I ran "Clean Up".