Hiding Sheets Based On Value In Dropdown Cell
Mar 21, 2007
I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.
I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.
if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.
View 14 Replies
ADVERTISEMENT
Jan 4, 2013
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
View 9 Replies
View Related
Mar 6, 2013
I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.
I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.
What I am trying to implement. I would have a drop down box in cell AD-4 with the current list
Select
Week 1
Week 2
Week 3
Week 4
What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6
also
If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5
so basically hide the weeks that are after the current selected week.
In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).
(the first row is the current Row labels, I have a lot of current hidden columns).
A
B
J
K
O
Supervisor Name
Agent Name
Needs Complete
Completed w/o Turn in
Needs Turn In
[Code] ........
View 7 Replies
View Related
Mar 26, 2014
I'm trying to write macro for hiding columns based on a drop down answer. The drop down would be in cell B7 on Sheet 1. The colums affected would be in each worksheet in the workbook. The data validation would have a drop down selection choice of 1 through 16. The columns affected range would be H through W. For example, in the drop down if 1 was selected, only column H would show. If two, H and I would show...etc. This macro should affect all worksheets in the workbook EXCEPT sheet 1.
View 2 Replies
View Related
Aug 26, 2009
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
View 4 Replies
View Related
Nov 4, 2008
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
View 9 Replies
View Related
Jan 5, 2013
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.
View 3 Replies
View Related
May 4, 2013
I have a document to track funding and would like to be able to auto-populate individual task sheets within the workbook based on a task drop-down. So if I choose Safety from the "Task Area" drop down it would auto populate the full row from the Master Sheet to the Safety Sheet. I have uploaded my document so you can better understand.
Funding Roll-up for 5.1.xlsx
View 1 Replies
View Related
Nov 10, 2008
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
View 14 Replies
View Related
Jul 9, 2014
I am trying to search the cells in Column A around rows 54 to 77. And if the cell says "Yes" it should hide the row. I think I'm on the right track, but can't seem to get it work.
[Code] .....
View 6 Replies
View Related
Aug 7, 2011
I am trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?
Sub GraphC()
Dim a As Integer
Dim ColumnVar As Variant
ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")
[Code] .........
View 8 Replies
View Related
Mar 20, 2012
Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)
Yes, this is an activeX checkbox
Code:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Dosing").Range("BM21").Value = 0 Then
CheckBoxLD.visable = False
Else: CheckBoxLD.visable = True
End If
End Sub
View 4 Replies
View Related
Dec 27, 2012
I have numerous tabs in this file, but all of the tabs all summarize to the first tab called "Annual Record".
So after "Annual Record", tabs follow as "WO1", "WO2", etc...
On the tab called "WO1", I have cell AJ5, which can have 3 status selected (Data Validation List), which is either Inactive, Open, or Completed. If cell AJ5 on "WO1" says "Inactive", then I want a named range on "Annual Record" to be hidden. Right now I have the range named "WorkOrder1". This named range is essentially rows 4-7. So if it's easier to hide rows vs. a named range, then so be it. I also would like it to happen automatically (perhaps what you would refer to as a change event). When cell AJ5 is change to either "Open" or "Completed", then the rows would unhide. I'm thinking that this would be repeated for tab "WO2". If cell AJ5 says "Inactive", then rows 8-11 on the tab "Annual Record" or the named range "WorkOrder2" would be hidden. And again, if the cell AJ5 is change to anything other than "Inactive", then the rows or range would unhide.
View 9 Replies
View Related
Mar 5, 2009
I need to hide 8 rows (30-37) based on a value in cell B28.
If the value is 2 then only show rows 30 and 31
If the value is 3 then only show rows 30, 31 and 32
Then continue this up to the user puts in the value of 8 and no rows are hidden.
View 9 Replies
View Related
Sep 7, 2009
I'm trying to come up with some code that will hide a number of rows within the range row 7 to row 15 based upon the value of cell G1.
e.g if G1 is 1 rows 8-15 are hidden, if it is 2, rows 9-15 are hidden i.e the number of rows visible within that range equals the value of G1.
View 9 Replies
View Related
May 27, 2014
I have seen a few threads here about this, but not for how I need it to work.
I will have a drop down list with 8 options, (Days of the week Wed to Wed, hence the 8 days lol) There will now be 8 columns, one for each day.
I want to be able to select the day from the list, and then have it hide all the columns except for the day selected. When a different day is picked, un hide that one and hide the rest.
I will attach a book in the reply...
View 14 Replies
View Related
Mar 20, 2014
the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.
Q Header
R Header
S Header
50
50
50
[Code]....
View 4 Replies
View Related
Jun 26, 2014
I have a macro that I am trying to add a feature to at the end of the code that hides blank rows. I have tried numerous methods based upon other forums, but my code does not seem to work and does not hide blank rows. In red is the section of code that I am having trouble with.
Sub Update()
Dim c As Object
Dim rngA As Range
Dim cc As Object
Dim AA As Range
'Check every cell in the range for matching criteria.
[Code] ...........
View 3 Replies
View Related
Dec 1, 2009
I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up:
E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.
Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.
Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.
View 9 Replies
View Related
Oct 20, 2006
I have a worksheet that has >10 rows of data and over 150 columns. The values in the cells for individual row include NA, NE, D, A and S. Each row will have one or all of these values in one of the cells. Here is the question:
How do I hide columns containing 'NA' in the cell for a particular row, when only that row is selected by clicking on any cell in row 'A'? For example: If my row 3 cell values for column A, J, R, and X are 'NA' I want to hide the column containing 'NA' only and display everything else? And only when I click on row 3 column 'A'
View 2 Replies
View Related
May 24, 2013
I used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.
View 2 Replies
View Related
May 6, 2009
I'm relatively new to this, and its entirely possibly (more likely probable) that I am attempting to this in the wrong way, but here's what I'm dealing with...
View 8 Replies
View Related
Jul 2, 2009
I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:
>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6
>> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6
View 9 Replies
View Related
Jul 25, 2014
I wanted to hide/remove the already selected options in a dependent drop down list so that the next time i am left with only the remaining options.
View 7 Replies
View Related
Dec 22, 2011
In the coming months the company i work for will be transferring from Excel 2003 to Excel 2010 (i know, a little late......) and now i am testing some things at home.
We deliver lots of Excel reports to our clients where we are using Pivot tables. In excel 2003 we where able to hide items from the dropdowns using properties - hide items but when i now open an excel 2003 file in excel 2010 and want to filter the pivot table to (let's say) another month i see all my hidden items.
Is there an option in excel 2010 to hide items like i could do in 2003? (either regular option or VBA)
View 4 Replies
View Related
Jun 11, 2009
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.
Sub Button10_click()
Worksheets("Account Type").Activate
End Sub
Sub Button11_Click()
Worksheets("Name Change").Activate
End Sub
Sub Button12_Click()
Worksheets("Address-Phone").Activate
End Sub
Sub Button13_Click()
Worksheets("Main Menu").Activate
End Sub
Sub Button15_Click()
Worksheets("Cust-Owner").Activate
End Sub
Sub Button16_Click()
Worksheets("Misc").Activate
End Sub
View 9 Replies
View Related
Aug 19, 2013
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.
View 6 Replies
View Related
Jan 8, 2013
Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.
Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?
View 1 Replies
View Related
Jun 3, 2013
I wrote a macro that hides every row that contains an "X" in column X for every sheet. The problem is that it runs very slowly (assumedly because it is checking every cell on every sheet). Is there a way to clean this up and make it run faster?
My code:
Sub HideRows()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim Cell As Range
For Each Cell In ws.Range("X:X")
If Cell.Value = "X" Then
Cell.EntireRow.Hidden = True
End If
Next
Next ws
End Sub
View 2 Replies
View Related
Aug 8, 2008
Why this doesn't error but yet it doesn't work either.
Sheets("2 plans, 3 plans, 4 plans").Visible = False
Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True
View 9 Replies
View Related
