Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

issue is that 29th Feb of a non-leap year year doesn't resolve to a date,
and could raise an exception if not handled correctly
you could try the 0th day of March ...
eg
DAY(DateSerial(2008, 3, 0))
will be 28 or 29 for leap year



"TheQuickBrownFox" wrote in message
...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

On Sat, 13 Jun 2009 17:11:14 +0100, "Patrick Molloy"
wrote:

issue is that 29th Feb of a non-leap year year doesn't resolve to a date,

Yes it does. In EVERY case, it will resolve to march 1st, so the test
that we all use, which DOES work, is to test for the month number that a
given excel internal date number resolves to, so if you declare a date,
and test it for the month number, a Feb 29 date of ANY leap year results
in a 3 for the month number.

As in:

=IF(MONTH(A29+1)=2,A29+1,NA())

Were "A29" refers to a 28th row (and date reference) in a data table.

It could easily ask about a specific date reference as well, instead of
a cell's contents.

and could raise an exception if not handled correctly
you could try the 0th day of March ...
eg
DAY(DateSerial(2008, 3, 0))
will be 28 or 29 for leap year

We have the test solved. What I need is the response action segment.





"TheQuickBrownFox" wrote in message
.. .

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

type 29/2/2009 into a cell, say A1. in another cell type =MONTH(A1) and
you'll get #Value

Certainly DateSerial(year,month,0) will always return the last day of the
preceding month

"Archimedes' Lever" wrote in message
...
On Sat, 13 Jun 2009 17:11:14 +0100, "Patrick Molloy"
wrote:

issue is that 29th Feb of a non-leap year year doesn't resolve to a date,

Yes it does. In EVERY case, it will resolve to march 1st, so the test
that we all use, which DOES work, is to test for the month number that a
given excel internal date number resolves to, so if you declare a date,
and test it for the month number, a Feb 29 date of ANY leap year results
in a 3 for the month number.

As in:

=IF(MONTH(A29+1)=2,A29+1,NA())

Were "A29" refers to a 28th row (and date reference) in a data table.

It could easily ask about a specific date reference as well, instead of
a cell's contents.

and could raise an exception if not handled correctly
you could try the 0th day of March ...
eg
DAY(DateSerial(2008, 3, 0))
will be 28 or 29 for leap year

We have the test solved. What I need is the response action segment.





"TheQuickBrownFox" wrote in message
. ..

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that
test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

Look, I already told you (an indicator that you do not read responses)
That I HAVE a successful test for leap year. What I need is the action
code to HIDE or UNHIDE a row based on that test result.

You keep coming back talking about leap year. I ALREADY know about leap
year. Re-read my original reply, and you will see that.


On Sat, 13 Jun 2009 18:44:10 +0100, "Patrick Molloy"
wrote:

type 29/2/2009 into a cell, say A1. in another cell type =MONTH(A1) and
you'll get #Value

Which is essentially what I already said by using 2/28 + 1. You still
have to have an action based on the test result, which is what this
thread asked for.

Certainly DateSerial(year,month,0) will always return the last day of the
preceding month

Which is worthless, and requires more code to complete the test
resolve. The internal engine is already in place. I use that. It works.
The test checks the result of the internal date engine's workings.

The missing code responds to that result.

"Archimedes' Lever" wrote in message
.. .
On Sat, 13 Jun 2009 17:11:14 +0100, "Patrick Molloy"
wrote:

issue is that 29th Feb of a non-leap year year doesn't resolve to a date,

Yes it does. In EVERY case, it will resolve to march 1st, so the test
that we all use, which DOES work, is to test for the month number that a
given excel internal date number resolves to, so if you declare a date,
and test it for the month number, a Feb 29 date of ANY leap year results
in a 3 for the month number.

As in:

=IF(MONTH(A29+1)=2,A29+1,NA())

Were "A29" refers to a 28th row (and date reference) in a data table.

It could easily ask about a specific date reference as well, instead of
a cell's contents.

and could raise an exception if not handled correctly
you could try the 0th day of March ...
eg
DAY(DateSerial(2008, 3, 0))
will be 28 or 29 for leap year

We have the test solved. What I need is the response action segment.





"TheQuickBrownFox" wrote in message
...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that
test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

OUCH

"Archimedes' Lever" wrote in message
...
Look, I already told you (an indicator that you do not read responses)
That I HAVE a successful test for leap year. What I need is the action
code to HIDE or UNHIDE a row based on that test result.

You keep coming back talking about leap year. I ALREADY know about leap
year. Re-read my original reply, and you will see that.


On Sat, 13 Jun 2009 18:44:10 +0100, "Patrick Molloy"
wrote:

type 29/2/2009 into a cell, say A1. in another cell type =MONTH(A1) and
you'll get #Value

Which is essentially what I already said by using 2/28 + 1. You still
have to have an action based on the test result, which is what this
thread asked for.

Certainly DateSerial(year,month,0) will always return the last day of the
preceding month

Which is worthless, and requires more code to complete the test
resolve. The internal engine is already in place. I use that. It works.
The test checks the result of the internal date engine's workings.

The missing code responds to that result.

"Archimedes' Lever" wrote in message
. ..
On Sat, 13 Jun 2009 17:11:14 +0100, "Patrick Molloy"
wrote:

issue is that 29th Feb of a non-leap year year doesn't resolve to a
date,

Yes it does. In EVERY case, it will resolve to march 1st, so the test
that we all use, which DOES work, is to test for the month number that a
given excel internal date number resolves to, so if you declare a date,
and test it for the month number, a Feb 29 date of ANY leap year results
in a 3 for the month number.

As in:

=IF(MONTH(A29+1)=2,A29+1,NA())

Were "A29" refers to a 28th row (and date reference) in a data table.

It could easily ask about a specific date reference as well, instead of
a cell's contents.

and could raise an exception if not handled correctly
you could try the 0th day of March ...
eg
DAY(DateSerial(2008, 3, 0))
will be 28 or 29 for leap year

We have the test solved. What I need is the response action segment.





"TheQuickBrownFox" wrote in
message
m...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide
a
row in two different worksheets in a workbook. So the script would
test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that
test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the
user
to perform the hide/unhide operation while knowing or after reading
the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

See if this helps. Change format to suit.

Sub FINDMAR1()
On Error GoTo NOMO
Dim MYDATE As String
Dim MD As Range
ActiveSheet.Rows.Hidden = False
MYDATE = Format(DateSerial(Year(Date), 3, 1), "MM/DD/YY")
Set MD = Columns("A").Find(What:=MYDATE, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Day(MD - 1) = 29 Then Rows(MD.Row - 1).Hidden True
NOMO:
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TheQuickBrownFox" wrote in message
...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

On Sat, 13 Jun 2009 12:20:56 -0500, "Don Guillett"
wrote:

See if this helps. Change format to suit.

Sub FINDMAR1()
On Error GoTo NOMO
Dim MYDATE As String
Dim MD As Range
ActiveSheet.Rows.Hidden = False
MYDATE = Format(DateSerial(Year(Date), 3, 1), "MM/DD/YY")
Set MD = Columns("A").Find(What:=MYDATE, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Day(MD - 1) = 29 Then Rows(MD.Row - 1).Hidden True
NOMO:
End Sub

Wrong. Nice try though, thanks.

You remind me of an old Cincinnati Red I used to watch play.

No, I do not need to search though any data.I do not need to set any
formats.

I do NOT need to do ANY searching of my data table The leap year test
ALREADY works and uses the internal date engine.

ALL I need to do is HIDE or UNHIDE ONE row on two different sheets,
based on the value my KNOWN TEST returns.

I currently use hard assigned "buttons" with no test result reference.

I do not know how to look at a test result in code, or assign a variable
based on one, then perform an action based on that. I am VB code dumb.

I DO know how to perform the leap year test. My current code segments
are simple and obvious as to function.They are for hard assigned, user
operated buttons. I want it to be automated. Button calls follow:


Sub LeapUnHide()
Rows("42:42").Select
Selection.EntireRow.Hidden = False
End Sub

Sub NotLeapHide()
Rows("42:42").Select
Selection.EntireRow.Hidden = True
End Sub
Sub LeapAnnualUnHide()
Rows("70:70").Select
Selection.EntireRow.Hidden = False
End Sub
Sub NotLeapAnnualHide()
Rows("70:70").Select
Selection.EntireRow.Hidden = True
End Sub


This works perfectly because ALL I want to do is to HIDE or UNHIDE ONE
ROW in ONE TABLE on two sheets. I could simply use named sheets since
these buttons only work on the current sheet when utilized, and that
would be bad if used on other months.

For a month I asked about leap year handling and got near no response.
I figured it out with some help that was there. Now I ask about
something else and get flooded with leap year handling routines that have
nothing to do with what I asked for.

I know beggars can't be choosers, but at least they do not get a box of
coal when they ask for a piece of bread.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Archimedes' Lever" wrote in message
...
On Sat, 13 Jun 2009 12:20:56 -0500, "Don Guillett"
wrote:

See if this helps. Change format to suit.

Sub FINDMAR1()
On Error GoTo NOMO
Dim MYDATE As String
Dim MD As Range
ActiveSheet.Rows.Hidden = False
MYDATE = Format(DateSerial(Year(Date), 3, 1), "MM/DD/YY")
Set MD = Columns("A").Find(What:=MYDATE, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Day(MD - 1) = 29 Then Rows(MD.Row - 1).Hidden True
NOMO:
End Sub

Wrong. Nice try though, thanks.

You remind me of an old Cincinnati Red I used to watch play.

No, I do not need to search though any data.I do not need to set any
formats.

I do NOT need to do ANY searching of my data table The leap year test
ALREADY works and uses the internal date engine.

ALL I need to do is HIDE or UNHIDE ONE row on two different sheets,
based on the value my KNOWN TEST returns.

I currently use hard assigned "buttons" with no test result reference.

I do not know how to look at a test result in code, or assign a variable
based on one, then perform an action based on that. I am VB code dumb.

I DO know how to perform the leap year test. My current code segments
are simple and obvious as to function.They are for hard assigned, user
operated buttons. I want it to be automated. Button calls follow:


Sub LeapUnHide()
Rows("42:42").Select
Selection.EntireRow.Hidden = False
End Sub

Sub NotLeapHide()
Rows("42:42").Select
Selection.EntireRow.Hidden = True
End Sub
Sub LeapAnnualUnHide()
Rows("70:70").Select
Selection.EntireRow.Hidden = False
End Sub
Sub NotLeapAnnualHide()
Rows("70:70").Select
Selection.EntireRow.Hidden = True
End Sub


This works perfectly because ALL I want to do is to HIDE or UNHIDE ONE
ROW in ONE TABLE on two sheets. I could simply use named sheets since
these buttons only work on the current sheet when utilized, and that
would be bad if used on other months.

For a month I asked about leap year handling and got near no response.
I figured it out with some help that was there. Now I ask about
something else and get flooded with leap year handling routines that have
nothing to do with what I asked for.

I know beggars can't be choosers, but at least they do not get a box of
coal when they ask for a piece of bread.

On Sat, 13 Jun 2009 15:33:38 -0500, "Don Guillett"
wrote:

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


My first post has a link to my spreadsheet in it.

One way to hide rows automatically is to use Workbook_SheetActivate
event. Copy Sub Workbook_SheetActivate and Function leapyear code below
into ThisWorkBook Module. then move to any sheets and check this would
work or not.
but I'm not sure if this could satisfy your request.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ye As Long
Dim shFeb As Worksheet, shAnu As Worksheet
ye = Range("BloodYear")
Set shFeb = Worksheets("February Tests")
Set shAnu = Worksheets("Annual Blood Pressure Data")
If leapyear(ye) Then
shFeb.Rows(42).Hidden = False
shAnu.Rows(70).Hidden = False
Else
shFeb.Rows(42).Hidden = True
shAnu.Rows(70).Hidden = True
End If
End Sub

Function leapyear(ByVal year As Long) As Boolean
If ((year Mod 4) = 0 And (year Mod 100) < 0) Or (year Mod 400) = 0 Then
leapyear = True
Else
leapyear = False
End If
End Function

Keiji

Archimedes' Lever wrote:
On Sat, 13 Jun 2009 15:33:38 -0500, "Don Guillett"
wrote:

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


My first post has a link to my spreadsheet in it.

Your file had no info or macros. If you want help, you may? want to follow
my request..... Your choice. An improvement in attitude may also help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Archimedes' Lever" wrote in message
...
On Sat, 13 Jun 2009 15:33:38 -0500, "Don Guillett"
wrote:

If desired, send your file to my address below along with this msg
and
a clear explanation of what you want and before/after examples.


My first post has a link to my spreadsheet in it.

add this code to the sheet "User Info" code page

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Bloodyear").Address Then
checkLeapYear Target.Value
End If
End Sub

Private Sub checkLeapYear(yr As Long)
Dim bLeapYear As Boolean
If yr Mod 4 = 0 Then
If yr Mod 100 = 0 Then
If yr / 100 Mod 4 = 0 Then
bLeapYear = True
End If
Else
bLeapYear = True
End If
End If
Worksheets("February Tests").Rows(42).Hidden = Not bLeapYear
End Sub


"TheQuickBrownFox" wrote in message
...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

Will the VB engine not perform the MONTH() function?

If one takes ANY given date of 2/29/xxxx and does the month test,it
returns a 2 for a leap year, and it returns a 3 for a non-leap year.
Every time, ANY year selected. I do not need to perform the same tests
that the internal engine already performs.

So, IF the MONTH function is available, then a simple test of
2/29/BloodYear will give the result from which we can determine a logical
true or false switch from.

So it would be like IF MnthTstReslt = true
then unhide else hide that row that sheet, AND on the annual sheet.

So, let's see if I can piece it together from what you have given me
here.

I'll post back what I come up with to see if you can further resolve it.

If the month function test is not available to VB, then your routine IS
a definitive test



On Sun, 14 Jun 2009 19:45:43 +0100, "Patrick Molloy"
wrote:

add this code to the sheet "User Info" code page

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Bloodyear").Address Then
checkLeapYear Target.Value
End If
End Sub

Private Sub checkLeapYear(yr As Long)
Dim bLeapYear As Boolean
If yr Mod 4 = 0 Then
If yr Mod 100 = 0 Then
If yr / 100 Mod 4 = 0 Then
bLeapYear = True
End If
Else
bLeapYear = True
End If
End If
Worksheets("February Tests").Rows(42).Hidden = Not bLeapYear
End Sub


"TheQuickBrownFox" wrote in message
.. .

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

"I do not need to perform the same tests that the internal engine already
performs."

you asked a question and frankly, were pretty blunt to several of us when we
tried to ascertain what it was that you needed.
The code I gave below solves the problem that you raised, yet you still
insist on dissing the help offered.
That just sucks my friend.

"Archimedes' Lever" wrote in message
...
Will the VB engine not perform the MONTH() function?

If one takes ANY given date of 2/29/xxxx and does the month test,it
returns a 2 for a leap year, and it returns a 3 for a non-leap year.
Every time, ANY year selected. I do not need to perform the same tests
that the internal engine already performs.

So, IF the MONTH function is available, then a simple test of
2/29/BloodYear will give the result from which we can determine a logical
true or false switch from.

So it would be like IF MnthTstReslt = true
then unhide else hide that row that sheet, AND on the annual sheet.

So, let's see if I can piece it together from what you have given me
here.

I'll post back what I come up with to see if you can further resolve it.

If the month function test is not available to VB, then your routine IS
a definitive test



On Sun, 14 Jun 2009 19:45:43 +0100, "Patrick Molloy"
wrote:

add this code to the sheet "User Info" code page

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Bloodyear").Address Then
checkLeapYear Target.Value
End If
End Sub

Private Sub checkLeapYear(yr As Long)
Dim bLeapYear As Boolean
If yr Mod 4 = 0 Then
If yr Mod 100 = 0 Then
If yr / 100 Mod 4 = 0 Then
bLeapYear = True
End If
Else
bLeapYear = True
End If
End If
Worksheets("February Tests").Rows(42).Hidden = Not bLeapYear
End Sub


"TheQuickBrownFox" wrote in message
. ..

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that
test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

the change to the Annual Data sheet was to remove the "hard" dates except
Jan 1st and replace with

A12: =A11+1
and replicate down

that takes care of the leap years.
for what its worth
probably too simplistic and already discarded by the questioner i expect. ho
hum

"Patrick Molloy" wrote in message
...
add this code to the sheet "User Info" code page

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Bloodyear").Address Then
checkLeapYear Target.Value
End If
End Sub

Private Sub checkLeapYear(yr As Long)
Dim bLeapYear As Boolean
If yr Mod 4 = 0 Then
If yr Mod 100 = 0 Then
If yr / 100 Mod 4 = 0 Then
bLeapYear = True
End If
Else
bLeapYear = True
End If
End If
Worksheets("February Tests").Rows(42).Hidden = Not bLeapYear
End Sub


"TheQuickBrownFox" wrote in message
...

Hey guys,

I have a test in a cell that examines if 2-29-xxxx (where xxxx is year
of choice) resolves to a month number of 3 or 2 which is a way of
determining a leap year. After such a test, I want to hide or unhide a
row in two different worksheets in a workbook. So the script would test
the value of the cell where I resolve a logical for the leap year (or
that can be done in the macro as well, no?), and then based on that test,
hide or unhide one row in the February worksheet, and the annual
worksheet. This has the effect of auto-magically adjusting the charts
that refer to these two sheets, which is a lot easier than trying to
dynamically adjust both the charts and the sheets. So, the hiding and
unhiding IS the right way to go, unless you have some kind of "Ignore
Row" function for the data or the chart. :-)

I previously assigned a hide and unhide button and tied it to a macro
segment, in those two sheets. That works, but it still requires the user
to perform the hide/unhide operation while knowing or after reading the
conditional leap year test results cell contents.

I guess I need an "IF "such and such" is true, THEN hide row such and
such in sheet such and such, and hide row such in such in other sheet
such and such, ELSE UNhide yada yada yada.

This is the sheet, minus the macros:

http://office.microsoft.com/search/r... 101436151033

On Mon, 15 Jun 2009 14:29:50 +0100, "Patrick Molloy"
wrote:

the change to the Annual Data sheet was to remove the "hard" dates except
Jan 1st and replace with

A12: =A11+1
and replicate down


Which fails between a 365 day year and a 366 day year.

It is a separate data sheet, so the hide/unhide call has to be acted on
that sheet as well.

The dates are explicit calls, not N+1. The chart carries dates and gets
the test data from lookups to the original month sheets, INCLUDING the
2/29 row.

The chart automatically sizes correctly when the sheets have 2/29 row
hidden. So does the February chart. It doesn't get any more simple than
to just make a hide and unhide routine based on an already performed leap
year test for the February and Annual data sheet.