VBA To Hide Rows Does Not Work With DropDown

Aug 21, 2014

Goal: Hide rows based on value in Drop Down Box

Problem: The code works fine if i manually change the $D$10. If I make changes to the Drop Down Box the cell link updates, but the rows are not hidden.

[Code] .....

View 6 Replies


ADVERTISEMENT

Hide Rows From Dropdown

May 22, 2009

i have a sheet with three dashboards built in and want to dispay on certain data depending on what is selected in a drop down menu (E8)

All - All rows are unhidden
UKIRSA - Rows 133 to 192
Glasgow - Rows 72 to 92
Sykes - Rows 12 to 70
Header - Rows 1 to 11
Footer - Rows 193 onwards

I want the header and footer to remain the same but the middle body of the dashboard to change depending on the selected option ie only the rows for the option are displayed, eg if Sykes selected then Glasgow and UKIRSA ranges are hidden

This is the code i am trying but it is not working:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "E8" Then
Select Case UCase(Target.Value)
Case "Sykes": Rows("12:70").Hidden = False
Case "Sykes": Rows("72:192").Hidden = True

Case "Glasgow": Rows("12:70").Hidden = True
Case "Glasgow": Rows("72:132").Hidden = False

Case "UKIRSA": Rows("134:192").Hidden = False
Case "UKIRSA": Rows("12:132").Hidden = True

Case "All": Rows("12:192").Hidden = False
End Select
End If
End Sub

View 9 Replies View Related

Hide Rows Based On Dropdown Selection?

Jan 8, 2005

I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.

View 9 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 Rows With Variable Text Based On Dropdown Selection

Jun 11, 2014

I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:

A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"

This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"

I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then

[Code]....

But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.

View 1 Replies View Related

Dropdown List (DV) Doesn't Work Same As Typing In Cell

Aug 26, 2013

I have written code in which cell color changes according to cell value. To change cell value I have used drop down list (Data/Validation/List). Main question/problem is code works well when cell value is changed by typing from keyboard only.

Code doesn't work when cell value is changed using drop down list.

Attached is the test file : Test_26082013.xls

Also refer link [URL] .....

View 1 Replies View Related

When Hide Hyperlinked Tab - Button Does Not Work?

Apr 15, 2013

I have a workbook full of several tabs. The main tab is the 'Report' tab, all other tabs are hidden. The information on the hidden tabs populate onto the 'Report' tab.

My question is, I have created a shape on the 'Report' tab to act as a button. This "buttton" is which I have hyper-linked with one of the hidden tabs. When the hyper-linked tab is showing, the button works fine. However, when I hide the hyper-linked tab, the button does not work. I would prefer to have just the 'Report' tab showing.

View 5 Replies View Related

Excel 2010 :: Dropdown Calendar To Work When Click In Any Cell In Specified Column

May 2, 2014

I am currently using a pop up calendar in Excel 2010 that with CTL+SHIFT+B that a calendar pops up, you select your date, and then the calendar closes. What I now need is to write a code or formula or something that when I click in any cell in column F lets say that the calendar opens automatically in that cell and then still close automatically after the date selection is made.

View 3 Replies View Related

Hide Standard Buttons & Stop Work Sheet Flashing..

Mar 10, 2009

1: Is their anyway I can get rid of the File, Edit, View, etc buttons at the top of the document so everyone that opens it can not see them? and also the save button, the idea is they have to use the button to save the sheet.

2: I have a button on the sheet with a macro that saves the sheet once it has been worked on, the sheet flashes when this button is clicked is there any way I can stop the sheet flashing ?

View 2 Replies View Related

Excel 2010 :: Dropdown Data Validation Menu Doesn't Work When Opened On Different Computer

Aug 19, 2013

Using Excel 2010, I set up a simple workbook with a spreadsheet on one page and the ranges for data validation on another. I successfully linked the fields in the "Category" and "Tender" columns with the data on the second sheet, making nice drop down menus. Everything works fine on my computer. However, when we open the workbook on a different station, the drop down menus disappear. If you highlight any of the "Category" or "Tender" fields, the little drop down prompt is missing; they behave like regular, un-formatted fields. It appears that the data validation became un-linked.

We tried opening versions with certain fields locked (Mainly the Total, Breakdown, and Summary areas, but the fields with data validation were still editable), and also completely unlocked documents. Every time, the drop down lists were lost when opened on different stations.

I've attached the completely unlocked version : 00_FLEX TRAVEL EXPENSE REPORT_v3.xlsx‎

Forget to check a box or something? This seems way harder for me to figure out that it should be!

View 3 Replies View Related

Hide Specific Rows In Sheet1 And Hide 5 More In Sheet2?

May 26, 2014

I have a macro in which i can enter the rows i want to hide.

If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].

Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.

View 14 Replies View Related

VBA - Button Command To Auto-Hide Rows With Value HIDE

Jun 9, 2013

Sub Button294_Click()

If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub

View 4 Replies View Related

Hide Combo Boxes (dropdown List)?

Nov 14, 2008

I trying to do a sheet with one combo box (dropdown list) where I want the user to be able to choose "add rows" to make more boxes appear. My idea to solve this was to put all the boxes in the sheet, and then create a macro that either hides or shows the rows with the extra boxes. Now I have a problem that hiding the rows just does that, and only that. The rows disappear, but the combo boxes stay visible (but ends up on top of eachother).

View 2 Replies View Related

Excel 2010 :: Dropdown Box To Hide Columns

Jan 31, 2012

I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...

I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.

If that is possible I would then like a second drop down box that allows the user to select a specific week.

Q1 is columns AW:DI.

I'm using excel 2010.

View 1 Replies View Related

Unhide/Hide Column When Choosing Dropdown List

Oct 5, 2009

Is there a way to have a column unhide one choosing a certain value from a dropdown list (validation)? I want the column to be hidden throughout the worksheet until a specific value is chosen in the list. Once it's chosen then I want it to unhide that column.

View 4 Replies View Related

Dropdown List With Prompt Box To Enter Value And Hide Input

Feb 5, 2013

I am looking to allow a user to enter a unique value when a drop down value is captured from a list i.e 'Other' (Possibly a pop up box), then I require this value to be hidden and calculated along with others to show the average (The average calculation obviously wouldn't be in the same cell).

View 8 Replies View Related

Hide Combobox Dropdown List During Long Macro

Mar 4, 2008

I've done some looking around in the object browser and in the forum, but i wasn't able to find the answer to this question:

I've got a combobox (dropdown list) in a userform which i use to run macros. soft of them are fairly long (2-3seconds), and during that time the dropdown list remains visible.... is there a way to force hide it?

View 6 Replies View Related

Hide / Show Cells By Dropdown Button On Parent Cell?

Aug 5, 2014

I want an option, if click on drop down button on parent cell than few a cells will come out in which I have mentioned details of the parent cell.

By pressing the same button again those cells will be hidden or covered under parent cell. ​

View 14 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 / Unhide Columns In Multiple Worksheets Based On Item Chosen In Dropdown List?

Feb 26, 2014

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?

View 14 Replies View Related

Hide Blank Rows Based On Values In Other Rows

Nov 3, 2006

I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

View 2 Replies View Related

Formula Does Not Work On Some Rows

Aug 11, 2014

I have some problem with my excel formula here. It works in some rows but doesn't in others.

I have attached the excel file herewith : Book1.xlsx

View 4 Replies View Related

Deleting The Unwanted Rows But Nothing Seems To Work?

Mar 6, 2013

I have a spreadsheet that only contains 1500 rows but excel acts like it is is using the full number of rows available. I have a print area defined and I have tried deleting the unwanted rows but nothing seems to work.

View 2 Replies View Related

Make Macro Work In Many Rows

Jan 31, 2007

I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.

Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:

If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................

View 4 Replies View Related

Auto Fill Doesn’t Work, But Trying To Avoid Manual Entry Of Rows.

Dec 31, 2009

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)

Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!

However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?

To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?

View 3 Replies View Related

Remove Rows Based On Value Of Dropdown Box?

Jan 30, 2014

I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.

View 7 Replies View Related

Adding Rows To Dropdown Menu

Nov 28, 2011

I created a Validation list from 2 to 20. I want to have it (the list) add a row every time you select the proper data.

Example: If I want to add 2 row I would select the 2 in the drop down menu. If I need 5 rows I need to select 5 from the drop down menu.

Is this a VBA code? Or can I use a formula?

View 5 Replies View Related

Same Dependent Dropdown List In Multiple Rows

Jul 4, 2013

I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.

I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.

When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.

Is there any way to have a three column dependent drop list that can be used in more than one row?

I have uploaded the file here: ownCloud

The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.

View 1 Replies View Related

Hide Rows With No Value?

Jul 17, 2014

I have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.

[Code] .....

View 9 Replies View Related

Hide Rows With VBA

Oct 6, 2008

I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved