I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.
I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.
I am trying to create some code that will work each time a cell is updated. For simplicity, when cell A1 on Sheet 1 is greater than or equal to 25,000, I want Sheet 2 and 3 to be shown (they will already have been hidden). When A1 on Sheet 1 changes to less that 25,000, I want only Sheet 4 and 5 to be shown. I want to be able to change the number back and forth in A1 so only Sheets 2 and 3 show when A1 is >= 25,000, and only Sheets 3 and 4 to be shown then A1 < 25,000. I can't seem to find how to hide sheets based on a cell changing. I can only find how to permanently hide sheets.
View 9 Replies View RelatedI have a sheet called "Summary", there is a cell, AR8, of that sheet that is linked to other sheets. If AR8 of "Summary" has a value other than 0 I would like it to unhide. But if the value goes back to 0 again I would like it to hide itself again.
View 6 Replies View RelatedI have a master sheet where users can change the name of 20 different sheets in the workbook by changing a cell value on the master sheet. Here is the code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
If Target.Cells.Count > 1 Then Exit Sub
[Code]....
The next step which I am having issues with is hiding the sheets. I want all sheets in the workbook to be hidden except for the master. I only want the other sheets to be visble if the user inputs a name on the master sheet.
So if the cell contents on the master sheet, say "B9", is blank, the sheet in the workbook that corresponds to that cell will remain hidden. If the user inputs anything, say "Sheet1", in cell "B9" on the master sheet, I want that sheet to become unhidden and to be named "Sheet1"
Develop a macro that will enable me to hide or unhide worksheets.
Basically, I have a few worksheets in my workbook and I want to have a page at the front with two buttons. One for Unhide sheet and the other for Hide sheet. Once activated, I want it to give me a list of worksheets that I can click to hide or unhide (depending on the button i click).
I have a button (A) that will unhide worksheet A. I would like to add another button (B) that will hide(very hidden) worksheet A (if allready open) then open worksheet B, vice versa when click button A.
Code:
Sub ShowSheets()
With Worksheets("A")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
End Sub
I have an Excel 2010 file with 10 worksheets. I would like to set up two VBA codes to attached to a two button; one to hide specific worksheets, the other to unhide. The specific worksheets are:
Calculation Sheet
GL Receipt
Sheet2
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
i want to hide my sheet if A1.value = 0(zero) and if unhide the sheet if A1.value = greater than 0(zero)
View 11 Replies View RelatedNeed a macro which will hide rows having 0 values. On running once it will hide those rows having 0 values and on pressing second time it will unhide those hidden rows and so on.
View 14 Replies View RelatedI want to hide & unhide columns using only (plus minus) button which is appearing on top of current sheet. I have couple of files with that options built in, but I dont know how to do it. I tried to see macros but appears that there is no macros at all in that file??
View 3 Replies View Related code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"[code].....
I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide
Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?
I have a speadsheet that has 27 columns for time entries, however employees seldom need to use more than the first 12 columns. I need to protect the sheet but would like the employees to be able to Unhide the columns when they need to use them, then reHide them. I can't find this as an option in either 2003 or 2007.
View 2 Replies View RelatedI would like to keep the current macros that I have and add the following:
A macros that can link the check boxes with the corresponding categories and rows in the other two sheets. If the box is checked the corresponding rows in the other sheets should appear.
I have color matched the check boxes with the rows on the two other sheets I would like to be linked up with.
When my spreadsheet opens it automatically protects the sheets using the following
With Sheet1
.Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
.EnableOutlining = True
End With
and I use the following to hide the column if the toggle button on my form is 'true'. The problem is it gives an error if the sheet is protected. it worksfine without protection.
If ToggleButton1.Value = True Then
Range("column_calories").EntireColumn.Hidden = True
Else
Range("column_calories").EntireColumn.Hidden = False
End If
In the attached abbreviated example, what should the second line in each of the three macros read to direct the hiding/unhiding of rows in the adjacent sheet? Right now I'm trying a If/Then configuration but it's not working. I was able to figure this out using grouped check boxes but a list box should be graphically "cleaner" and hopefully simpler in coding. The original table list is hidden in column "A", if that is important. I could use a combo box but the menu list is short and I think the list box approach will be simpler.... If there is a way to combine the three macros into one.
View 2 Replies View RelatedThere 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.
I'm familiar with the code to allow grouping/ungrouping in a protected spreadsheet; http://www.ozgrid.com/Excel/outlining-protected.htm. How would I code to allow for hiding/unhiding cloumns and rows while still protecting the spreadsheet? Also, how could i allow for this file to be shared? Currently i'm getting an debug error when another person enters the file.
View 2 Replies View RelatedWhen team number is entered, excel must show or hide the the team e.g when you enter 5 as the required team number then only 5 teams appear and the rest of the teams are hidden.
I have attached excel file that has the layout. Teams.xlsx
need to create a macro that will hide/unhide rows. I have a list of items that need to be completed and under each item is another list that explains how to complete the items on the first list.
Let’s say for example, How to bake a cake
I want rows 2 – 6 to be hidden when the file is opened and then unhide when A1 is clicked. Then hide again when A1 is clicked again.
I have a huge list of items that need to work in this format.
The other thing I need is when the document is printed all of the hidden items are printed.
I'm using column A as a reference. My data starts in cell A3.
Whenever theres a zero in any cell in column A I would like that row to hide.
If the row was hidden and the value changes to any number greater than zero I need the row to unhide.
I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
worksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.
I have draft VBA code as follows:
I need to write a macro that will hide and unhide columns based on a cells value. I know how to write it to hide and unhide rows, but i can figure it out for columns.
For the rows I am using the following:
Sub HideRowsSavings()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Savings #4")
LR = .Range("A" & Rows.Count).End(xlUp).Row
[Code] ......
I'm trying to find a way to hide rows based on a condition in one cell.
Basically I have a pull-down list with Yes or No in it. I want to hide rows 52 through 57, if No is selected, and make the rows reappear if Yes is selected.
I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.
View 9 Replies View RelatedI have a worksheet that contains 10 columns of data. In row 2 I have a formula that will display the result "x" if the data in that column should NOT be hidden, and for all columns of data without an "x" in row 2 I would like to hide the entire column.
Therefore, I am looking for a macro that looks across the range of cells E2:N2 and if there is not an "x" in the cell, then hide that column...
To make things a little more complicated, the value in row 2 will change when other values are amended on other worksheets that feed into this one and I will need the macro to 'unhide' the column as soon an "x" appears in row 2
I realize there are many Hide/Unhide requests. Although, believe me when I state that I have checked into the matter, and tried to solve it on my own.
If any of you fine forum dwellers could point me in the right direction, I would greatly appreciate it.
I am trying to achieve the following:
Two Macros:
#1:
To hide rows(entire spreadsheet) based on cell value in Column C (value is '0')
Here is a code (from this forum) that seems to be the least complex/confusing. I don't understand the "AC2" value. Would it not be A2? Or does it signify a certain range?
Sub HideRows()
With Sheets("Store Snapshot")
If Range("AC2").Value = 2 Then Rows("13:15").EntireRow.Hidden = True
If Range("AC2").Value = 1 Then Rows("13:15").EntireRow.Hidden = False
End With
End Sub
#2:
To UNHIDE the rows that Macro #1 Hid.
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:-
1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden.
2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden.
3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want:
If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F