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.

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" <david@peoriaconstruction.com> wrote in message
news:Ih9Wd.500$ZB6.452@newssvr19.news.prodigy.com...
> 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.
>
>

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!

"David Balkema" <test2@peoriaconstruction.com> wrote in message
news:uloHv33IFHA.1248@TK2MSFTNGP10.phx.gbl...
>
> 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

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!

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" <test2@peoriaconstruction.com> wrote in message
news:%23uXZ$RFJFHA.3500@TK2MSFTNGP14.phx.gbl...
>
> 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!