VBA Excel Macro worked for 2-years now dosen't??
Is there a limit on how much code you can place in a VBA file?=A0
All works well except the macro I call "VacUsed"=A0
It is called from a couple of procedures I post the last
procedure=A0"ThisWorkBook" use to close and save the workbook.=A0
Private Sub Workbook_BeforeClose(Cancel As Boolean)=A0
=A0 =A0 Call FilterTestOff=A0
=A0 =A0 Call VacUsed=A0
=A0 =A0 Call DeleteMenu=A0
=A0 =A0 Call AllProtect=A0
=A0 =A0 Sheets("VacationAccrued").Activate=A0
End Sub=A0
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As=A0Boolean)=A0
=A0 =A0 Call AllProtect=A0
=A0 =A0 Sheets("VacationAccrued").Activate=A0
End Sub=A0
And this is the VacUsed Procedure:=A0
Sub VacUsed()=A0
'=A0
' VacUsed Macro=A0
' Macro recorded 5/16/2008 by Geoffrey Feldman=A0
'=A0
' Stores "Vacation Days Taken" from Vacation Accured Sheet=A0
'=A0
=A0 =A0 Set Wkb =3D ActiveWorkbook=A0
=A0 =A0 Set ShtA =3D Wkb.Worksheets("VacationAccrued")=A0
=A0 =A0 inLRw =3D ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row=A0
=A0 =A0 Set ShtS =3D Wkb.Worksheets("VacUsedStorage")=A0
=A0 =A0 ShtS.Activate=A0
=A0 =A0 Call shUnprotect=A0
=A0 =A0 ShtS.Range("B2:C1000").ClearContents=A0
' =A0Update VacUsed Names from VacAccrue=A0
=A0 =A0 ShtS.Range("B2:B" & inLRw - 1).Value =3D ShtA.Range("B3:B"
&=A0inLRw).Value=A0
' =A0Update VacUsed Days Taken from VacAccrue=A0
=A0 =A0 ShtS.Range("C2:C" & inLRw - 1).Value =3D ShtA.Range("I3:I"
&=A0inLRw).Value=A0
=A0 =A0 ShtS.Columns("B:C").EntireColumn.AutoFit=A0
=A0 =A0 Range("B2").Select=A0
=A0 =A0 Application.CutCopyMode =3D False=A0
=A0 =A0 Call shProtect=A0
=A0 =A0 ShtA.Activate=A0
=A0 =A0 Range("B3").Select=A0
End Sub=A0
The macro skips the call "shUnProtect" which is needed to continue
the=A0
update process=A0
Your expert help would be appreciated=A0
|
0 |
|
Canlink
3/13/2010 5:17:30 PM
excel.programming
6508 articles. 
2 followers.
11 Replies
1086 Views
There is a limit of 64K in a module, so try splitting the procedures across
multiple modules.
--
HTH
Bob
"Canlink" <canlink@gmail.com> wrote in message
news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com...
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 Procedure:
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
|
|
0 |
|
Bob
3/13/2010 5:57:20 PM
How do you find the current size of a module? Robert "Bob Phillips" <bob.phillips@somewhere.com> wrote in message news:uTDsiatwKHA.3536@TK2MSFTNGP06.phx.gbl... > > There is a limit of 64K in a module, so try splitting the procedures > across multiple modules. > > -- >
|
|
0 |
|
Robert
3/13/2010 9:14:29 PM
"Bob Phillips" <bob.phillips@somewhere.com> wrote:
> There is a limit of 64K in a module, so try splitting
> the procedures across multiple modules.
64K what?
Also, what would I type into VBA Help to discover this and any other
limitations?
Nothing I tried seems to work. But it's easy to overlook the obvious with
all the seemingly irrelevant links that a Help search often spits out.
PS: It is unusual, even for MS, for a product to regress in limitations.
So why would >64K whatever work 2 years ago, but not now, if that is indeed
the problem? Or are you assuming "Greg House" rules? ;-)
----- original message -----
"Bob Phillips" <bob.phillips@somewhere.com> wrote in message
news:uTDsiatwKHA.3536@TK2MSFTNGP06.phx.gbl...
> There is a limit of 64K in a module, so try splitting the procedures
> across multiple modules.
>
> --
>
> HTH
>
> Bob
>
> "Canlink" <canlink@gmail.com> wrote in message
> news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com...
> 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 Procedure:
> 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
>
|
|
0 |
|
Joe
3/13/2010 10:15:38 PM
On Mar 13, 5:15=A0pm, "Joe User" <joeu2004> wrote:
> "Bob Phillips" <bob.phill...@somewhere.com> wrote:
> > There is a limit of 64K in a module, so try splitting
> > the procedures across multiple modules.
>
> 64K what?
>
> Also, what would I type into VBA Help to discover this and any other
> limitations?
>
> Nothing I tried seems to work. =A0But it's easy to overlook the obvious w=
ith
> all the seemingly irrelevant links that a Help search often spits out.
>
> PS: =A0It is unusual, even for MS, for a product to regress in limitation=
s.
> So why would >64K whatever work 2 years ago, but not now, if that is inde=
ed
> the problem? =A0Or are you assuming "Greg House" rules? ;-)
>
> ----- original message -----
>
> "Bob Phillips" <bob.phill...@somewhere.com> wrote in message
>
> news:uTDsiatwKHA.3536@TK2MSFTNGP06.phx.gbl...
>
>
>
> > There is a limit of 64K in a module, so try splitting the procedures
> > across multiple modules.
>
> > --
>
> > HTH
>
> > Bob
>
> > "Canlink" <canl...@gmail.com> wrote in message
> >news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com..=
..
> > 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 Procedure:
> > Sub VacUsed()
> > '
> > ' VacUsed Macro
> > ' Macro recorded 5/16/2008 by Geoffrey Feldman
> > '
> > ' Stores "Vacation Days Taken" from Vacation Accured Sheet
> > '
> > Set Wkb =3D ActiveWorkbook
> > Set ShtA =3D Wkb.Worksheets("VacationAccrued")
> > inLRw =3D ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row
> > Set ShtS =3D Wkb.Worksheets("VacUsedStorage")
> > ShtS.Activate
> > Call shUnprotect
> > ShtS.Range("B2:C1000").ClearContents
> > ' Update VacUsed Names from VacAccrue
> > ShtS.Range("B2:B" & inLRw - 1).Value =3D ShtA.Range("B3:B"
> > & inLRw).Value
> > ' Update VacUsed Days Taken from VacAccrue
> > ShtS.Range("C2:C" & inLRw - 1).Value =3D ShtA.Range("I3:I"
> > & inLRw).Value
> > ShtS.Columns("B:C").EntireColumn.AutoFit
> > Range("B2").Select
> > Application.CutCopyMode =3D 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
I always use more than one module, I learned also that their is a
limit on the size of a module, but I did not know it was 64K and I do
not know how to measure the size of each module.
The "shProtect" procedure is part of the standard module I use for
numerous applications. The "VacUsed" procedure is again separate and
only consists of a total of three procedures unique to this
spreadsheet.
|
|
0 |
|
Canlink
3/14/2010 12:01:05 AM
Export a module and note any change in file size before and after? I have no idea if that will tell you anything<g> Gord Dibben MS Excel MVP On Sat, 13 Mar 2010 16:01:05 -0800 (PST), Canlink <canlink@gmail.com> wrote: >I always use more than one module, I learned also that their is a >limit on the size of a module, but I did not know it was 64K and I do >not know how to measure the size of each module. >The "shProtect" procedure is part of the standard module I use for >numerous applications. The "VacUsed" procedure is again separate and >only consists of a total of three procedures unique to this >spreadsheet.
|
|
0 |
|
Gord
3/14/2010 12:30:08 AM
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" <canlink@gmail.com> wrote in message
news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com...
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 Procedure:
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
|
|
0 |
|
Peter
3/14/2010 11:37:07 AM
On Mar 14, 7:37=A0am, "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 mor=
e
> 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 m=
uch
> 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" <canl...@gmail.com> wrote in message
>
> news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com...
> 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 Procedure:
> Sub VacUsed()
> '
> ' VacUsed Macro
> ' Macro recorded 5/16/2008 by Geoffrey Feldman
> '
> ' Stores "Vacation Days Taken" from Vacation Accured Sheet
> '
> Set Wkb =3D ActiveWorkbook
> Set ShtA =3D Wkb.Worksheets("VacationAccrued")
> inLRw =3D ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row
> Set ShtS =3D Wkb.Worksheets("VacUsedStorage")
> ShtS.Activate
> Call shUnprotect
> ShtS.Range("B2:C1000").ClearContents
> ' Update VacUsed Names from VacAccrue
> ShtS.Range("B2:B" & inLRw - 1).Value =3D ShtA.Range("B3:B"
> & inLRw).Value
> ' Update VacUsed Days Taken from VacAccrue
> ShtS.Range("C2:C" & inLRw - 1).Value =3D ShtA.Range("I3:I"
> & inLRw).Value
> ShtS.Columns("B:C").EntireColumn.AutoFit
> Range("B2").Select
> Application.CutCopyMode =3D 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 =3D ShtA.Range("B3:B"
> & inLRw).Value" with an error that states you have not unprotected the wo=
rksheet.
This is extremely confusing to me and is not good for my client
relations either.
|
|
0 |
|
Canlink
3/14/2010 5:03:03 PM
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 addin
http://www.appspro.com/Utilities/CodeCleaner.htm
Regards,
Peter T
"Canlink" <canlink@gmail.com> wrote in message
news:f63477d3-8d5b-48d8-b7bb-488634ce1406@t20g2000yqe.googlegroups.com...
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" <canl...@gmail.com> wrote in message
>
> news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com...
> 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 Procedure:
> 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.
|
|
0 |
|
Peter
3/14/2010 5:43:52 PM
On Mar 14, 1:43=A0pm, "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 clea=
n
> the project with Rob Bovey's CodeCleaner addinhttp://www.appspro.com/Util=
ities/CodeCleaner.htm
>
> Regards,
> Peter T
>
> "Canlink" <canl...@gmail.com> wrote in message
>
> news:f63477d3-8d5b-48d8-b7bb-488634ce1406@t20g2000yqe.googlegroups.com...
> 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 m=
ore
> > 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 ca=
n
> > 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 "does=
n't
> > work"
>
> > Regards,
> > Peter T
>
> > "Canlink" <canl...@gmail.com> wrote in message
>
> >news:f7a88471-4367-412d-ba11-9fd760cecfa6@q16g2000yqq.googlegroups.com..=
..
> > 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 Procedure:
> > Sub VacUsed()
> > '
> > ' VacUsed Macro
> > ' Macro recorded 5/16/2008 by Geoffrey Feldman
> > '
> > ' Stores "Vacation Days Taken" from Vacation Accured Sheet
> > '
> > Set Wkb =3D ActiveWorkbook
> > Set ShtA =3D Wkb.Worksheets("VacationAccrued")
> > inLRw =3D ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row
> > Set ShtS =3D Wkb.Worksheets("VacUsedStorage")
> > ShtS.Activate
> > Call shUnprotect
> > ShtS.Range("B2:C1000").ClearContents
> > ' Update VacUsed Names from VacAccrue
> > ShtS.Range("B2:B" & inLRw - 1).Value =3D ShtA.Range("B3:B"
> > & inLRw).Value
> > ' Update VacUsed Days Taken from VacAccrue
> > ShtS.Range("C2:C" & inLRw - 1).Value =3D ShtA.Range("I3:I"
> > & inLRw).Value
> > ShtS.Columns("B:C").EntireColumn.AutoFit
> > Range("B2").Select
> > Application.CutCopyMode =3D 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" =A0it 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 =3D ShtA.Range("B3:B"> & inLRw).Va=
lue" 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".
|
|
0 |
|
Canlink
3/14/2010 6:51:36 PM
>How do you find the current size of a module? You really can't. The 64K limit is on the size of "compiled" code, not the size to the text source code. (VBA code is never stored as text within the workbook. It is stored in an intermediate byte-code language called OpCodes -- similar in theory to Java -- and at runtime or when you choose Compile from the Debug menu, VBA converts the OpCodes, which are version/platform neutral, to ExCodes, which are version specific and feeds those ExCodes to the VBA interpreter runtime which executes machine code on behalf of VBA based on the ExCodes. What you see on the editor screen as text code is the OpCodes translated to text for display.) Exporting the module to a text file and looking at the size of that file might give you a crude approximation of the compiled size, but I wouldn't give it much credibility. As far as I know, the 64K limit isn't publicly documented. It was revealed to an MVP by a Softie and has propagated via usenet. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal" <nobody@gmail.com> wrote: >How do you find the current size of a module? > >Robert > >"Bob Phillips" <bob.phillips@somewhere.com> wrote in message >news:uTDsiatwKHA.3536@TK2MSFTNGP06.phx.gbl... >> >> There is a limit of 64K in a module, so try splitting the procedures >> across multiple modules. >> >> -- >>
|
|
0 |
|
Chip
3/14/2010 7:22:02 PM
On Mar 14, 3:22=A0pm, Chip Pearson <c...@cpearson.com> wrote: > >How do you find the current size of a module? > > You really can't. =A0The 64K limit is on the size of "compiled" code,nott= he size to the text source code. (VBA code is never stored as > text within the workbook. It is stored in an intermediate byte-code > language called OpCodes -- similar in theory to Java -- and at runtime > or when you choose Compile from the Debug menu, VBA converts the > OpCodes, which are version/platform neutral, to ExCodes, which are > version specific and feeds those ExCodes to the VBA interpreter > runtime which executes machine code on behalf of VBA based on the > ExCodes. What you see on the editor screen as text code is the OpCodes > translated to text for display.) > > Exporting the module to a text file and looking at the size of that > file might give you a crude approximation of the compiled size, but I > wouldn't give it much credibility. As far as I know, the 64K limit > isn't publicly documented. It was revealed to an MVP by a Softie and > has propagated via usenet. > > Cordially, > Chip Pearson > Microsoft Most Valuable Professional, > =A0 =A0 =A0 =A0 Excel, 1998 - 2010 > Pearson Software Consulting, LLCwww.cpearson.com > > On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal" > > > > <nob...@gmail.com> wrote: > >How do you find the current size of a module? > > >Robert > > >"Bob Phillips" <bob.phill...@somewhere.com> wrote in message > >news:uTDsiatwKHA.3536@TK2MSFTNGP06.phx.gbl... > > >> There is a limit of 64K in a module, so try splitting the procedures > >> across multiple modules. > > >> -- I would like to thank all that posted their comments. Rob Bovey's CodeCleaner add-in did the work!! THANKS TO ALL amd to Rob Bovey's code.
|
|
0 |
|
Canlink
3/18/2010 2:55:38 PM
Similar Artilces:
Convert Excel Tables to Pivot Table Lists
Excel Tables to Pivot Lists Hello, I'm trying to convert excel tables into pivot table lists and I am looking for a method to do this besides cutting and pasting. The table has 6 columns (see below) with count of product for each year e.g. xxx1 prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a 4 column list like, (see "Get into pivot table list in this form) Thanks Home....Prod....Yr1....Yr2....Yr3....Yr4 xxx1.....prod1....100....200....300....400 xxx2.....prod2....110....210....310....410 xxx1.....prod3....120....220....320....420 xxx2.....prod4....130....2...
Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...
Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...
Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...
Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...
Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...
Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...
How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...
Outlook needs Outlook Express to work
I've installed my Outlook 2002 on my new Vista PC (I had Xp before). Now when I start Outlook it says "requires Outlook Express 4.01 or greater". I've been looking for Outlook Express to download and install but no joy. No CD came with my new Vista PC. Any ideas, short of buying a more modern Outlook? Vista doesn't actually support Outlook 2002 or older. Outlook Express is part of Internet Explorer but with the advent of Vista, it was switched to Windows Mail. There is no more Outlook Express. You could attempt however to create a profile using workgroup or c...
Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...
Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...
Personal Macro Workbook
I had some macro stored in the personal macro workbook but now I can not find the workbook. When I try to do a new macro and store it in the "Personal Macro Workbook" I get the error message "Personal Macro Workbook in the startup folder must stay open for recording". I click ok and get a message that I'm unable to record. I can record macro to other workbooks. What happened to the personal macro workbook ? Thanks ...
How To Copy MS Word mailing labels into Excel
I have a word doc that I want to put into Excel. I want to add some more fields to the names and addresses. Is this simple or do I have to learn how to program? Michael Rodriguez City of Grand Prairie Michael, have you tried to copy and paste the data into excel? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Michael Rodriguez" <mrodrigu@gptx.org> wrote in messa...
How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...
Grammar check not working
Hello, I am using Word 2007 and have a problem with grammar and punctuation errors. I deliberately put two spaces between words, do not put space after a comma, write long sentences and finish a sentence without a verb but the green underline never appears. The spell check is functioning properly, no problem with that. In Word Options > Proofing, "Mark grammar errors as you type" is selected. I changed that selection and tried again but it still did not work. I used different languages as default language but no change. I would be grateful if someone could come...
How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...
Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...
Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...
help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...
[b]Can I download Excel data to a MS Access database?[/b]
I've built an Excel 2002 form that I want our internal customers to access from our intranet, and use. Once completed, they will send it to us as an e-mail attachment. I'd like to be able to open it, and somehow download the data from the form into an MS Access 2002 database I've built (so that we don't have to rekey it into the database). Is this possible or even feasible? Any and all help is appreciated. Thanks. :D --------- Message sent via www.excelforums.com Hi in Access check 'File - Import External data' -- Regards Frank Kabel Frankfurt, Germany "...
Options > Clean Up Now
First there is no community for Outlook Express 6 - Hopefully a Microsoft person will respond. Under 'options' > (tab) 'Maintenance' > there is a button that reads 'Clean Up Now'. The explanation reads that it will delete all downloaded messages. What the #%&!@ does that mean? Will all emails I have previously read be deleted? Is it only deleting the deleted messages? What? -- Shawn That should only apply to downloaded newsgroup messages. The outlook express newsgroup (yes there is one!) will be better able to answer the question. http://www...
2 register ruinning together on one z report
Is this possible, to have two registers running on one z report ? We have a small store and would like to setup a second register as a backup, temporary addition ( during rush hours) Since we are a small show i do not need to have a separate register reports. Can two registers ring up all sales together as one ? How can i set this up ? No, you can't do that. You might be able to use the Register Analysis report available in SO Manager Utilities/Crystal Reports to get something close to a single Z though. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.c...
Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...
Custom Headings in Work Order and Sales Receipts
Where can I edit the headings of receipt formats so that when they are printed instead of "Work Order" in the right corner, I want it to say "Delivery Note" Instead of "Sales Receipt" I want it to say "Invoice" Also, how can I make a Work order print without each item taking up 3 lines... ie: I need it to print just like a quote or regular receipt, but be a work order? The receipts in RMS are saved as a .XML file. If you are somewhat familular with programming you will be able to navigate your way around the template and make some edits. typic...
Excel DNS query
Hello, Is there a way for an Excel function to query a DNS server? Thanks. Soundy Not that I know of, but you can turn on the macro recorder, use 'get external data' and tailor the resulting code into a user function of your own. E.g. I've used this to create a button to get MS-Access data from a query that has the same name as the sheet (tab) name. It saves me a lot of copy-paste actions. Bas Hartkamp <soundy@gmail.com> schreef in bericht news:1151940450.029823.127570@j8g2000cwa.googlegroups.com... > Hello, > > Is there a way for an Excel function ...
Excel Tables to Pivot Lists Hello, I'm trying to convert excel tables into pivot table lists and I am looking for a method to do this besides cutting and pasting. The table has 6 columns (see below) with count of product for each year e.g. xxx1 prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a 4 column list like, (see "Get into pivot table list in this form) Thanks Home....Prod....Yr1....Yr2....Yr3....Yr4 xxx1.....prod1....100....200....300....400 xxx2.....prod2....110....210....310....410 xxx1.....prod3....120....220....320....420 xxx2.....prod4....130....2...
Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...
Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...
Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...
Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...
Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...
Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...
How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...
Outlook needs Outlook Express to work
I've installed my Outlook 2002 on my new Vista PC (I had Xp before). Now when I start Outlook it says "requires Outlook Express 4.01 or greater". I've been looking for Outlook Express to download and install but no joy. No CD came with my new Vista PC. Any ideas, short of buying a more modern Outlook? Vista doesn't actually support Outlook 2002 or older. Outlook Express is part of Internet Explorer but with the advent of Vista, it was switched to Windows Mail. There is no more Outlook Express. You could attempt however to create a profile using workgroup or c...
Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...
Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...
Personal Macro Workbook
I had some macro stored in the personal macro workbook but now I can not find the workbook. When I try to do a new macro and store it in the "Personal Macro Workbook" I get the error message "Personal Macro Workbook in the startup folder must stay open for recording". I click ok and get a message that I'm unable to record. I can record macro to other workbooks. What happened to the personal macro workbook ? Thanks ...
How To Copy MS Word mailing labels into Excel
I have a word doc that I want to put into Excel. I want to add some more fields to the names and addresses. Is this simple or do I have to learn how to program? Michael Rodriguez City of Grand Prairie Michael, have you tried to copy and paste the data into excel? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Michael Rodriguez" <mrodrigu@gptx.org> wrote in messa...
How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...
Grammar check not working
Hello, I am using Word 2007 and have a problem with grammar and punctuation errors. I deliberately put two spaces between words, do not put space after a comma, write long sentences and finish a sentence without a verb but the green underline never appears. The spell check is functioning properly, no problem with that. In Word Options > Proofing, "Mark grammar errors as you type" is selected. I changed that selection and tried again but it still did not work. I used different languages as default language but no change. I would be grateful if someone could come...
How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...
Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...
Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...
help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...
[b]Can I download Excel data to a MS Access database?[/b]
I've built an Excel 2002 form that I want our internal customers to access from our intranet, and use. Once completed, they will send it to us as an e-mail attachment. I'd like to be able to open it, and somehow download the data from the form into an MS Access 2002 database I've built (so that we don't have to rekey it into the database). Is this possible or even feasible? Any and all help is appreciated. Thanks. :D --------- Message sent via www.excelforums.com Hi in Access check 'File - Import External data' -- Regards Frank Kabel Frankfurt, Germany "...
Options > Clean Up Now
First there is no community for Outlook Express 6 - Hopefully a Microsoft person will respond. Under 'options' > (tab) 'Maintenance' > there is a button that reads 'Clean Up Now'. The explanation reads that it will delete all downloaded messages. What the #%&!@ does that mean? Will all emails I have previously read be deleted? Is it only deleting the deleted messages? What? -- Shawn That should only apply to downloaded newsgroup messages. The outlook express newsgroup (yes there is one!) will be better able to answer the question. http://www...
2 register ruinning together on one z report
Is this possible, to have two registers running on one z report ? We have a small store and would like to setup a second register as a backup, temporary addition ( during rush hours) Since we are a small show i do not need to have a separate register reports. Can two registers ring up all sales together as one ? How can i set this up ? No, you can't do that. You might be able to use the Register Analysis report available in SO Manager Utilities/Crystal Reports to get something close to a single Z though. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.c...
Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...
Custom Headings in Work Order and Sales Receipts
Where can I edit the headings of receipt formats so that when they are printed instead of "Work Order" in the right corner, I want it to say "Delivery Note" Instead of "Sales Receipt" I want it to say "Invoice" Also, how can I make a Work order print without each item taking up 3 lines... ie: I need it to print just like a quote or regular receipt, but be a work order? The receipts in RMS are saved as a .XML file. If you are somewhat familular with programming you will be able to navigate your way around the template and make some edits. typic...
Excel DNS query
Hello, Is there a way for an Excel function to query a DNS server? Thanks. Soundy Not that I know of, but you can turn on the macro recorder, use 'get external data' and tailor the resulting code into a user function of your own. E.g. I've used this to create a button to get MS-Access data from a query that has the same name as the sheet (tab) name. It saves me a lot of copy-paste actions. Bas Hartkamp <soundy@gmail.com> schreef in bericht news:1151940450.029823.127570@j8g2000cwa.googlegroups.com... > Hello, > > Is there a way for an Excel function ...