Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Undo button and redo button quit | Excel Discussion (Misc queries) | |||
Inserting a Graphic on a Button when the Button is Assigned to a M | Excel Discussion (Misc queries) | |||
insert row above button (retrieve position of button) | Excel Discussion (Misc queries) | |||
How do I lock a radio button group if a N/A button is selected | Excel Discussion (Misc queries) | |||
Delete a custom button by holding down the ALT key and dragging the button off the toolbar | Excel Programming |