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!

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!

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" <test2@peoriaconstruction.com> wrote in message
news:urfD1PLJFHA.2356@TK2MSFTNGP14.phx.gbl...
>
> 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!

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!

"David Balkema" <test2@peoriaconstruction.com> wrote in message
news:ubOcjPPJFHA.588@TK2MSFTNGP15.phx.gbl...
> 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"