I have a cell that has a list in it which has the options: regular, extended, regular and extended. I would like the row below this list to only be visible if the previous mentioned cell has "regular and extended" selected. I asked a coworker that has excel experience to take a look at it and he said he couldn't get it to run.
Untested in the absence of your workbook but something like the following in a SheetChange event macro.
First name the cell that contains the drop down. It future proofs things for when you inevitably add some rows or columns above or to the left.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("mydropdown")) Is Nothing Then If LCase(Target) = "regular and extended" Then Target.Cells(2, 1).EntireRow.Hidden = False Else Target.Cells(2, 1).EntireRow.Hidden = True End If End If End Sub
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
This is the code that I have come up with but it won't run. Doesn't give any errors, just doesn't run.
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Input").Range("Post.Top.Bracket.Type").value = "REGULAR" Then
Rows("165:165").EntireRow.hidden = True
ElseIf Worksheets("Input").Range("Post.Top.Bracket.Type").value = "Extended" Then
Rows("165:165").EntireRow.hidden = True
ElseIf Worksheets("Input").Range("Post.Top.Bracket.Type").value = "Regular and Extended" Then
Rows("165:165").EntireRow.hidden = False
End If
End Sub
[QUOTE=Joe.RBI;4817504]This is the code that I have come up with but it won't run. Doesn't give any errors, just doesn't run.
Why are you not following the example I gave and ignoring the advice? There is no need to name the sheet since you are using a sheet change event. The sheet name is irrelevant.
You are also hard coding a cell reference. That's not good practice. You were advised to use a range name.
[QUOTE=Richard Buttrey;4817545]I'm not sure I am following you. At no point did I ignore any advice, I tried your code and only changed the range value to the desired cell name. What do you mean by "hard coding a cell reference"? I have a named cell that has a list with three options that reference a separate tab.Originally Posted by Joe.RBI
Hello
Try this code in worksheet module
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$2" Then If Target.Value = "regular and extended" Then Rows(Target.Row + 1).Hidden = False Else Rows(Target.Row + 1).Hidden = True End If End If End Sub
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
I tried this and couldn't get it to run either. It worked on the test file you sent but won't run on the spreadsheet in question.Hello
Try this code in worksheet module
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$2" Then If Target.Value = "regular and extended" Then Rows(Target.Row + 1).Hidden = False Else Rows(Target.Row + 1).Hidden = True End If End If End Sub
Additional information:
The workbook has 5 tabs: INPUT, FIELDLIST, SEQUENCE, TABLE, Components
The desired tab I am trying to run this in is the "INPUT" Tab.
The cell that has the dropdown list in it is B164 but has been renamed to "POST.TOP.BRACKET.TYPE"
The row I would like to hide is 165.
There are multiple other macros working in this workbook but for some reason, I am unable to get any variation of this macro to run.
It will be better to upload sample of your workbook ..
The workbook is larger than 1,000kb file size limit. Is there an alternate way to attach it?
Last edited by Joe.RBI; 01-09-2018 at 04:36 PM.
copy your original file and delete most of the rows and also delete the sheets that doesn't relate to your request. Then compress the file with winrar and upload it
Here is the zip file with most unnecessary info removed.
Remove the filter then put the code in worksheet module
Private Sub Worksheet_Change(ByVal Target As Range) If Target.address = "$B$164" Then If LCase(Target.value) = "regular and extended" Then Rows(Target.Row + 1).hidden = False Else Rows(Target.Row + 1).hidden = True End If End If End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks