Capture Group/Outline Row Hide/Show
Apr 26, 2008
The following is how the data is displayed:
[JAN] [a] [FEB] [b] [MAR] [c] [Q1] [APR] [d] [MAY] [e] [JUN] [f] [Q2] etc.
Basically, it is months and quarter. However I have an additional column that is hidden after each month - [a], [b], [c] etc. Those are displayed whenever a cell in the previous column is clicked. Now, I select all the columns from [JAN] to [c] and group them and I have a + on the top most row.
Problem is when I click on +, it expands the month as well as [a] [b] [c]. I have a macro to hide these, but how do I trap the [+] [-] action and run the hiding macro after that?
View 5 Replies
ADVERTISEMENT
May 25, 2007
i have seen this cool feature in a workbook, but I am unable to find out how implement it myself. I can't really explain it (thus I am unable to find good search results) so I have added a little picture of what I mean. Its about adding controls for hiding and unhiding columns. How do I get such nice plusses and minusses into my workbook? I use Excel 2002
View 4 Replies
View Related
May 14, 2013
In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).
If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."
If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.
I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.
View 4 Replies
View Related
Sep 15, 2013
I know how to insert various groups of radio buttons on one sheet. They must be in their own group box control. The problem I have is that I don't see any option to turn off the resulting group box outline and label so it cannot be seen. Is there some way to do that in Excel 2010? I have 16 groups of two radio buttons across the top of the sheet and space is at a premium. If there was no outline it would look much nicer. The cells below the radio buttons function as the labels the way I am using them.
View 5 Replies
View Related
Aug 20, 2008
I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's
Private Sub Workbook_Open()
Dim cSheet As Integer
Dim tSheet As Worksheet
On Error Resume Next
cSheet = 1
Set tSheet = Worksheets(cSheet)
While Err.Number = 0
Call wbProtect(tSheet)
cSheet = cSheet + 1
Set tSheet = Worksheets(cSheet)
Wend
Err.Number = 0
End Sub..........................
View 2 Replies
View Related
Jul 25, 2006
I curently using the group and outline to subtotal certain data. I do not want to data to be modified. As a result I protect the particular sheet. Problem is once I protect the worksheet, I can't expand the data hidden (using the group & outline). Is there anyway that i can expand the data & in the same time protect the
data. A sample in enclose(without worksheet protected)
View 6 Replies
View Related
May 19, 2014
Is there any way to outline excel data automatically
In the attached excel sheet : test outline.xls
I need automatically Because my data very large ...
View 2 Replies
View Related
Oct 5, 2006
I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full
Sub ProtectAll()
Dim ws As Worksheet
sSheet = Control.Name
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case sSheet1
Case Else
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
End Select
Next ws
End Sub
View 2 Replies
View Related
Mar 19, 2008
I have two groups (outlines) of columns on the same sheet. the following line :
ActiveSheet.Outline.ShowLevels 0, 1
controls the two groups. What if I want different levels od detail for each group ?
TIA.
Daniel
View 2 Replies
View Related
Jul 10, 2012
I have a spreadsheet that contains an outline-like number in column A, except where there are values in Column C. What I want is to automatically outline based on column A and subtotal at each succesive outline level (i.e. I would like to be able to roll up the subtotals to variousl depths of the outline).
Code:
A B C
1.1first level
1.1.1Second level
1.1.1.1third level
value 5
1.2first level
1.2.1second level
value 7
value 4
1.2.2second level
1.2.2.1third level
value 6
View 2 Replies
View Related
Oct 9, 2009
There is 2 parts to my question and I will explain them as best as i can.
1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.
2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.
View 10 Replies
View Related
May 13, 2004
Is there a way to hide certain cells using the protect sheet option? Checking the hidden box on the format cells dialogue doesn't seem to do it. I know you can do it by entering ;;; in the custom formating but then if you have to remember which ones you did that too. I would like to be able to hide and unhide a group of cells easily.
View 9 Replies
View Related
Oct 8, 2008
I have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.
Then I want to group the clients, salesperson together so that the details can be hidden.
This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.
View 4 Replies
View Related
May 14, 2009
I have this tab that won't show the marks when I group the columns or rows.
I know it is grouped becuase if you copy the data to the blank tab the marks appear fine. Copying the data is an easy work around but I would really like to know the root cause of this. The issue was only with this tab. I used to have a ton of other tabs and they grouped fine.
View 3 Replies
View Related
Aug 28, 2007
Rows 1 & 2 are on a single sheet - there are 26 counties in all, in alphabetical order and divided into IPF groups (as shown on row 1).
On a second sheet I have a data validation list showing IPF1, IPF2, IPF3... etc.
In the columns beside the validation list I would like to show just those counties in that IPF group (example in rows 4 & 6).
View 2 Replies
View Related
Mar 11, 2014
I have a file sheet named "ratio summary." The data to this is linked to a number of different working sheets (not in the attachment for obvious reasons)
Currently the data in the ratio summary is grouped by year but "open" and the Graph worksheet is automatically linked to the data in row11.
However when I group the data by year (i.e. clicking the "-" figure) the graph also changes.
I haven't deleted the data so don't understand why the graph data is changing.
excel graph help.xls
View 3 Replies
View Related
Mar 27, 2013
I have a workbook with worksheet like 2a, 2b, 2c, 2d, 2e, 3a, 3b...etc Is it anyway i can make a tab which will hide a group of worksheet (like 2a,2b....2e) together and unhide all the worksheet with another click on it? becasue i got too many worksheet to show in the bottom..
View 2 Replies
View Related
Nov 14, 2013
Is there a way to hide a group of cells based on the value of a cell in another sheet?
I have attached my spreadsheet.
So, based on the selection in H5 on the 'Wood Selection' sheet, I would like to hide cells on the 'Moisture Controlled Schedule' sheet, and the 'Time Controlled Schedule' sheet.
If Ponderosa Pine is selected, I want it to hide all the douglas-fir info, and vice versa
View 2 Replies
View Related
Dec 20, 2007
We have a file that needs to be updated monthly by running macros, but the macros are being blocked by protection. I would like to put some code at the start of the macro that unlocks all sheets and then at the end locks them up again. Actually, all I really need is a macro to do just that unlock all sheets, and then another one that locks them back up again as we will need to lock and unlock at various times and it is annoying to have to do it by sheet ....
View 14 Replies
View Related
Mar 30, 2014
get my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
View 8 Replies
View Related
Mar 17, 2009
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
View 9 Replies
View Related
Mar 10, 2006
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
View 9 Replies
View Related
Dec 29, 2006
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
View 5 Replies
View Related
Jun 1, 2007
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies
View Related
Jul 24, 2007
Is there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies
View Related
Jan 10, 2014
I have a some data in 1st column in which most of the data has decimals. For ex. 209.8, 224.4 like this. I have converted it by roundup function and get the exact result. I want to hide the decimals means not show decimals. Only the result will show like. 210, 224 etc. For your reference I have attached the sheet. I have manually typed this in the C column result area. That I need in A column.
View 9 Replies
View Related
Jan 5, 2007
Is it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
View 9 Replies
View Related
Apr 2, 2009
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.
Example
If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.
View 2 Replies
View Related
Jul 27, 2013
I've seen excel sheet view like this [URL].....
View 2 Replies
View Related
Mar 8, 2014
I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code: [URL]....
View 7 Replies
View Related
