Hide, unhide macro

[TheQuickBrownFox]

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/redir.aspx?assetid=TC300058731033&respos=27&pid=CT101436151033

 

[Patrick Molloy]

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

 

[Archimedes' Lever]

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.

 

[Don Guillett]

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

 

[Patrick Molloy]

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]

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.

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]

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.

 

[Don Guillett]

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.

 

[Archimedes' Lever]

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.

 

[keiji kounoike]

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

 

[Don Guillett]

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.

 

[Patrick Molloy]

OUCH

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.




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.


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.

 

[Patrick Molloy]

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

 

[Archimedes' Lever]

Your file had no info or macros.

Apparently the literacy macro in your brain is turned off.

Did you even bother to examine the February or annual worksheet at all?
If not, why are you claiming to have a brain, and to be helping people
here?

If you want help, you may? want to follow
my request.....

You made no request. You made a non-compliant suggestion.

Your choice. An improvement in attitude may also help.

The macro texts are right there on the first worksheet, just like the
description states. SINCE Microsoft does NOT allow macro enable upload,
I included it as TEXT on that first sheet. All you would need to do is
incorporate the text into a macro, which is also what I said.

My attitude is bad?

Your goddamned cursory glance is far worse for someone claiming to be
helping.

I am not looking for your boilerplate cut and paste response, and if
you cannot bend your brain long enough to create a ten line script for
someone that simply doesn't have familiarity with the current syntax
compared to his old database days, I think it is your attitude that
should get some personal focus. You are attempting to morph what I have
into what you think might work instead of finishing what I have, which is
far simpler. I am morph resistant. I like simple.

It is a simple If Then Else thing, and my lack of familiarity with VB
is all that keeps me from hashing it out.

As was already explained twice, I do NOT need ANY testing for ANY day.

I have ALREADY derived that logical value.

 

[Archimedes' Lever]

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

 

[Patrick Molloy]

"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.

 

[Patrick Molloy]

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

 

[Archimedes' Lever]

"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.

LOOK at the topic of this thread. LOOK at my original post. Just
because some retard twenty years ago said that capital letters meant
shouting, it DOES NOT. It connotes an emphasis. If you are not mature
enough to garner the proper gist from the text, I cannot help you.

That is not being blunt, that is steering someone that obviously needs
steering, despite having all the steering he needed in the first post.
The correct response should have been "Duh! How'd I miss that?" But
Nooooooo.... I get even MORE friggin leap year routines.

My question would be where were all the leap year routines last month
when THAT is what I was asking for? Nobody got it right then either.

I could have hunted for ages and finally come up with it, as I am sure
it has been hashed over already, but I THOUGHT that I could ask folks
that I WAS sure had more familiarity with the code base. I didn't
realize that some of you had lead guitarist mentalities. Ask for one
riff, and get a "what do you think of this" kind of response.

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.

What I asked for was the hide unhide routine, and that quite explicitly.
It was absolutely simple. You would be demoted if you were on my help
staff.

What everyone (including you) seems to insist on offering is a leap
year test and routine. AND AS I HAVE STATED SEVERAL TIMES AS A RESULT, I
do not need to perform a leap year test. I already have that. I need to
perform a hide and unhide of a row based on:

the contents of cell ABC123. If 3 hide, if 2 unhide. It really was
that simple. Your script had the calls and structure I was looking for,
despite all the added glitz

I am sorry that you are so immature that you took offense to my response
when I was offered exactly what I said I didn't need.

How much more simple could it get? Where is the answer to the question
of whether or not the VB engine recognizes the MONTH function?

As I also stated, I can likely put together what I need from what you
posted. Rest assured, however, that I never "dissed" your help. The
gang boy lingo is pretty lame. I will "diss" that stupid crap.

The code I gave below solves the problem that you raised, yet you still
insist on dissing the help offered.

Read what I said. Me telling you that I can likely get what I want
from what you posted is NOT "dissing" your help. It is drawing the best
elements from it and shit-canning the rest!

You gang boy mentality kids have a bent perception about life. You do
not DESERVE respect, idiot. You EARN it. That happens when you GIVE
without question.

That just sucks my friend.

Something does, and it has to do with that vast difference between the
mental and physical age of today's working youth. Age: Between 22 and
35. Mental age: Between 13 and 17, at best.

Ending it with "my friend" is as bad as Obama denying that he knew
Ayers, or what he did. There is a vital lesson for you to learn here,
but I am not sure that you will recognize it properly, nor learn from it
if you do. Pull your pants up, and start buying pants that match your
waist line.

 

[Archimedes' Lever]

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.

 

[Archimedes' Lever]

For Patrick, or is it Patricia?

Where is the answer to the question
of whether or not the VB engine recognizes the MONTH function?

Not only would you NOT be allowed to continue on my help desk, but I'd
Donald Trump your ass out the door as well. Just like I said. I knew
that you would NOT learn the correct and needed enlightenment you were
given.

I expect to see a response that still carries gang boy retard lingo,
cries about respect, and continued lack of real help. Sure your name
isn't Patricia, bitich?