VBA To Hide/Unhide Sheets Based On Cell Value

Jun 2, 2008

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.

ADVERTISEMENT

How To Hide/unhide Rows Based On Cell Value

Jun 17, 2009

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.

View 14 Replies View Related

Hide & Unhide Columns Based On A Cell Value

Feb 21, 2010

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:

View 9 Replies View Related

Hide And Unhide Columns Based On Cell Value

Mar 4, 2013

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] ......

View 1 Replies View Related

Hide/Unhide Rows Based On 1 Cell

Jul 3, 2007

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.

View 9 Replies View Related

Hide/unhide Col Macro Based On Cell Value

Aug 27, 2009

I 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

View 9 Replies View Related

Hide & Unhide Rows Based On Cell Value

Feb 26, 2010

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.

View 9 Replies View Related

Hide/Unhide Columns Based On Cell Value

Sep 4, 2007

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.

View 9 Replies View Related

Unhide/Hide Sheet Based On Value In Cell

Apr 2, 2008

I 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 Related

Hide / Unhide Rows Based On Cell Value Containing Formula

Jun 17, 2014

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.

View 3 Replies View Related

Macro To Hide / Unhide Worksheet Based On Cell Value

Jul 18, 2010

I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.

I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?

View 9 Replies View Related

Hide / Unhide Columns Macro Based On Cell Value?

Dec 7, 2011

I want to hide and unhide columns based on a cell's value.

If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’
THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet
OTHERWISE Unhide columns D:K

View 4 Replies View Related

Hide / Unhide Rows Based On Calculated Cell?

Jul 12, 2013

I have a calculated cell (D13) which can either be (All), Grade or Rate.

If D13 = (All), I would like Rows 19:52 to be hidden
If D13 = Grade, Only Rows 19:20 to be hidden
If D13 = Rate, Only Rows 22:52 to be hidden

View 2 Replies View Related

Hide And Unhide Columns Based On Cell Value From Lookup

Dec 4, 2013

I am trying to hide/unhide columns based on what is return to cell C2 from this lookup (=VLOOKUP(B2,GM!E2:M129,9,FALSE)). It can only return 3,6 or 9

If it returns 3 I want to hide columns I:P
If it returns 12 I want to hide columns E:L
If it returns 6 I want to hide E:H and M:P

I have been trying to work with this code which I found on this forum but I haven't been able to get it working fully. It only works when C2 is entered rather than calculated and I can't get it working for the M:P part of the third option.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("C2")
If Not Intersect(Target, Changed) Is Nothing Then
Range("A:Z").EntireColumn.Hidden = False

[Code]...

View 1 Replies View Related

Macro To Hide And Unhide Rows Based On Cell Value

Jan 2, 2014

I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.

View 1 Replies View Related

Hide/unhide Number Of Columns Based On Cell Value

Jul 17, 2007

If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.

Each number is one column.

eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden

up to 200 columns.

I tried it as a select case, but it is limited to just 22 cases which obviously not enough.

View 9 Replies View Related

Macro To Hide/unhide Rows Based On Cell Value

Jun 17, 2003

Like to create a macro (button) to hide / unhide rows based on a cell value

Example

A1=No then hide roa A
C1=No then hide row c

View 9 Replies View Related

Rename Sheet And Hide / Unhide Based On Cell Contents?

Feb 10, 2014

I 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"

View 2 Replies View Related

Automatically Hide/Unhide Columns Based On Cell Text

Jan 10, 2009

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub

but I have a lot of columns that I need to perform as above and I have put the code together as below

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................

Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.

View 8 Replies View Related

VBA To UnHide / Hide Sheets

Feb 20, 2014

I have a workbook with about 20 worksheets in it.

6 are visible
3 are hidden

the remaining are very hidden

I'm creating a "welcome" page to the workbook with instructions on how to update data. The data between the 20 sheets consists of pivot tables, charts and summary data. The Visible sheets are data for management's review (all protected). The 3 hidden sheets are pivot tables that the user needs to pull data from and the very hidden sheets are not to be seen by anyone.

In my "welcome" page, I added the instructions of how to update data, but wanted an area where I could assign a checkbox or button to click on for the user to unhide my 3 hidden sheets (say Sheet1, Sheet2 and Sheet3). I do not want it to unhide my very hidden sheets. Then, when the information needed is retrieved from those sheets, I would like the user to use a checkbox or button to hide the 3 hidden sheets again. Is there a way to do this?

I tried creating custom view but couldn't do that because of the pivot tables (the option was disabled). I'm a beginner in VBA so don't even know how to begin.

View 6 Replies View Related

Hide/ Unhide Sheets With Macro

Nov 6, 2008

I want to run 2 different macros:

Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1

I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?

View 6 Replies View Related

Macro To Unhide Then Hide Sheets

Sep 29, 2008

I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.

I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:

Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'

This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again.

View 9 Replies View Related

Hide Sheets, Then Need Password To Unhide

Oct 6, 2008

See I have hidden my sheets and I need them to stay hidden.

But when I need to unhide them, a password should be needed to unhide them.

Maybe add a button on one of the open sheets that when pressed will unhide the sheets but will promt for password.

View 9 Replies View Related

Hide/unhide Selected Range In All Sheets

Aug 10, 2007

I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.

View 2 Replies View Related

Excel 2007 :: Hide And Unhide Sheets Using Checkboxes?

May 13, 2014

Using Excel 2007, I have a workbook with 7 sheets. The first one is a Navigation Page where I have checkboxes (form controls, not active X) with the names of the other 6 sheets. When the box(es) are checked, the sheet(s) become visible. I have accomplished this by assigning macros I recorded.

I need to now add the opposite: When the box(es) are unchecked, the sheets become hidden. From googling and looking for other threads/forums here, I gather that I need to add code/ VBA, but I know nothing about these at all.

View 2 Replies View Related

Excel 2013 :: How To Hide And Unhide Headings From All Sheets At Once

May 14, 2014

i want to hide and unhide headings from all sheets at once (Excel 2013)

View 8 Replies View Related

Hide Selected Sheets And Not Unhide Without Entering Password

Mar 18, 2014

I want to hide selected sheets and when i want to unhide them, ask for password to unhide the sheets.

View 10 Replies View Related

Capture Cells From One Sheet Into Another. Hide/Unhide Sheets

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

Macro To Unhide / Hide Sheets With Combobox Selection

Jun 18, 2007

I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.

The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.

The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.

I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;

How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?

View 9 Replies View Related

Hide Row Based On 0 Or Unhide When Less Than 0

Oct 16, 2013

Trying to hide a rows based on value being inserted to A1 = 0 (if value is >0, then unhide), but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 0 Then
Rows("33:42").EntireRow.Hidden = True
Else
Rows("33:42").EntireRow.Hidden = False
End If

View 6 Replies View Related

Hide & Unhide Rows Based On Condition

Aug 17, 2007

Attached is a portion of the file I will be working with, just to illustrate what I would like to do.

I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.

I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.

View 4 Replies View Related

How To Hide / Unhide Rows Based On Selection From Dropdown Box

Feb 1, 2013

I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...

Code that will allow you to unhide other worksheets in the same book????

View 1 Replies View Related

Hide / Unhide Cells Based On A Date Range

Mar 18, 2014

I am very new to VBA. I need to have a code that will hide/unhide cells based on a date range.

Cell A1 contains the formula for todays date, based on that answer, I want to hide/unhide columns based on a weekly date range.

Ex.:

Today is 03/18/2014 I want excel to say If A1(Today) is greater than 03/14/2014 and less than or equal to 03/21/2014 then unhide column E and hide the rest.

Then let's say Today is 03/22/2014. I want excel to say If A1(Today) is greater than 03/21/2014 and less than or equal to 03/28/2014 then unhide column F and hide the rest.

columns in questions are D-AC

View 3 Replies View Related

Hide/unhide Button Based On Data Validation

Oct 8, 2009

I am creating a disclaimer in one of my sheets.

Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.

Then I have a button below which covers rows18, 19 and 20.

Is there a way I can have this button hidden until Cell N15 = Yes?

View 10 Replies View Related

Macro To Hide/unhide Rows Based On A Returned Value

Feb 4, 2008

I need to do: Based on whether a cell returns "Export" from a vlookup function, I need a macro to run, hiding some cells and unhiding others at the same time. Also, if possible, if the cell's value changes from "Export" to something else, I need it to revert back to the original hidden/visible rows.

View 9 Replies View Related

Unhide/hide Multiple Columns Based On Date

Jul 30, 2007

I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.

View 8 Replies View Related

Hide Column Based On Another Sheets Cell Value

Apr 16, 2008

There are 2 sheets in the excel worksheet. in one sheet there would be drop downs
the 2nd sheet will have 4 columns A,B,C,D) Depending on the selection in sheet1. If a1 = Y then columnA in the 2nd sheet should be hidden. If a1 = N then columnB in the 2nd sheet should be hidden. how can this be done..The key is the columns to be hidden are on a different sheet and that is where I am having the difficulty.

View 3 Replies View Related

Hide / Unhide Rows And Columns Based On Several Date Ranges

Mar 19, 2014

I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.

My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.

View 4 Replies View Related

Hide/unhide Columns Based On Dropdown List Answer

Sep 26, 2008

I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.

I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.

What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.

Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.

View 9 Replies View Related

Hide And Delete Sheets Based On Cell Input?

Aug 11, 2014

I have a situation where i would like to hide and delete a few sheets in my worksheet.

there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.

the remaining irrelevant sheets i would like it to be hidden and deleted.

'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.

View 3 Replies View Related

Hide/Show Sheets Based On Cell Values & Validate Entry

May 30, 2008

excal VBA programming.I have attached the file name "help" for your easy explanation purpose.

1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.

2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.

View 5 Replies View Related

ADVERTISEMENT