Button Help
 

I've thought and worked on this all day without a successful solution, so be
warned, I'm not very good at this.

First, I'm trying to make it where I can click on a cell and have the
following happen:
1. The cell value will change from Detailed or Summary, whichever is
opposite from when I click on it.
2. As the value changes, a varying amount of lines (depending on how
many details
are in each section) will either go hidden or unhide. Again,
whichever is opposite.


I'm trying to see if there is a way to get all that to happen without using
a Commandbutton if possible (which I couldn't get to work successfully
either).

I have already hidden Column A where I have a one letter code to describe
each line as a heading, detail, or totals line. I want only the detail rows
to hide.

Any help would be greatly appreciated.

Button Help
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$H$1" Then
If Target.Value = "Detailed" Then
Target.Value = "Summary"
Else
Target.Value = "Detailed"
End If
With Target.Offset(1, 0).Resize(10, 1).EntireRow
.Hidden = Not .Hidden
End With
Target.Offset(0, 1).Activate
End If
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
. ..
I've thought and worked on this all day without a successful solution, so
be
warned, I'm not very good at this.

First, I'm trying to make it where I can click on a cell and have the
following happen:
1. The cell value will change from Detailed or Summary, whichever is
opposite from when I click on it.
2. As the value changes, a varying amount of lines (depending on how
many details
are in each section) will either go hidden or unhide. Again,
whichever is opposite.


I'm trying to see if there is a way to get all that to happen without
using
a Commandbutton if possible (which I couldn't get to work successfully
either).

I have already hidden Column A where I have a one letter code to describe
each line as a heading, detail, or totals line. I want only the detail
rows
to hide.

Any help would be greatly appreciated.

Button Help
 

Bob,

Thanks for your help. It worked great for the single section. My new
question becomes:

If I have multiple sections, each as a different category, how do I
program it to check cells, G7, G11, G24, etc. when there will always be
new sections added. How do I make the code more dynamic. I thought
about naming a range DetSum, which will include all the cells that would
change the result to either hide or show the detail lines. Then have
that range checked to see if the Target.Address is in that named range,
and if it is execute the code.

I'm not sure on how to dymanically change the named range and also refer
to that named range in a Union statement. I know these must be basic
questions, but I'm still learning. Thanks so much.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

"David Balkema" wrote in message
...

If I have multiple sections, each as a different category, how do I
program it to check cells, G7, G11, G24, etc. when there will always be
new sections added. How do I make the code more dynamic. I thought
about naming a range DetSum, which will include all the cells that would
change the result to either hide or show the detail lines. Then have
that range checked to see if the Target.Address is in that named range,
and if it is execute the code.

I think that is the way that I would do it. To check if the target is in
that range, you would use

If Not Intersect (Targt,Range("DetSum")) Is Nothing Then

rather than

If Target.Address = "$H$1" Then

I'm not sure on how to dymanically change the named range and also refer
to that named range in a Union statement. I know these must be basic
questions, but I'm still learning. Thanks so much.

Defining the range dynamically is the difficult bit. If it is just an
extending contiguous range, that is easy,, but your cells seem to be
scattered, so you have a problem. I am not sure why you feel you need to use
that named range in a Union statement, but it seems to me that you would
need to manually update the range name.

Bob

Button Help
 

Bob,

Thanks for all your help.

I have the named range now working. The only problem I'm having now is
if I add a section.

Is there a way to programatically add a cell to a named range? The
named range will only change when another macro is run, which will add
the section with the programming. So, I won't have to define the range
all the time, just add one here and there.

Any help would be greatly appreciated.

Thanks.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

You can redefine the range in VBA, but you have to know which cell to add.
How will you know when and what cell(s) to add to the range, that is what
event triggers the action?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David Balkema" wrote in message
...

Bob,

Thanks for all your help.

I have the named range now working. The only problem I'm having now is
if I add a section.

Is there a way to programatically add a cell to a named range? The
named range will only change when another macro is run, which will add
the section with the programming. So, I won't have to define the range
all the time, just add one here and there.

Any help would be greatly appreciated.

Thanks.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

Bob,

I have the code to add the new section to go to the last line, which is
the totals line for all the sections on the entire worksheet. From
there, it will go back up two rows and start inserting the new formating
for the section, which includes:

The Heading Row (where the detail / summary cell is located)
Two Generic Detail Rows
The Section Totals Row
One Blank Line

This provides a nice new, crisp section at the end of the sheet right
before the Page Totals line.

Therefore, I am trying to get, for example, Cell E25 added to the named
range DetSum.

Is there a way to do that? I've search for a way, but can't seem to
find any.

Thanks again for the help.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

David,

I still don't see how you know it is E25, but assuming that you do, then

Union(Range("DetSum"),Range("E25")).Name = "DetSum"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David Balkema" wrote in message
...

Bob,

I have the code to add the new section to go to the last line, which is
the totals line for all the sections on the entire worksheet. From
there, it will go back up two rows and start inserting the new formating
for the section, which includes:

The Heading Row (where the detail / summary cell is located)
Two Generic Detail Rows
The Section Totals Row
One Blank Line

This provides a nice new, crisp section at the end of the sheet right
before the Page Totals line.

Therefore, I am trying to get, for example, Cell E25 added to the named
range DetSum.

Is there a way to do that? I've search for a way, but can't seem to
find any.

Thanks again for the help.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

Bob,

Thanks again for all your help.

This is what I've gotten to work then:

Dim detrng As String
detrng = Selection.Address
Union(Range("Detailed"), Range(detrng)).Name = "Detailed"

The selected cell is the cell that has the code execute.

I changed the name of the named range, but other than that it should
work. The only thing I'm concerned with now is getting the reference to
update if a section is deleted. I'll work on that later. I've got
another part to work on a for awhile. Thanks again.

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Button Help
 

"David Balkema" wrote in message
...
Bob,

Thanks again for all your help.

This is what I've gotten to work then:

Dim detrng As String
detrng = Selection.Address
Union(Range("Detailed"), Range(detrng)).Name = "Detailed"

This is overdoing it a bit. You are going back to go forward :-). You only
need

Union(Selection, Range(detrng)).Name = "Detailed"