VBA Code For Hiding Lines Based on Selection From Dropdown

Question

Hello,

Looking for some help to hide rows. I've tried several VBA codes, but seem to be doing something wrong. I already have 1 module in the sheet that is active. Not sure if that is causing the issue, but looks like the coding allows for multiple modules.

Basically I am looking to do this:

If cell B141 has "Yes" then rows 292 to 659 are hidden

If cell B143 has "yes" then rows 148 through 291 AND rows 446 through 659 are hidden.

If cell B146 has "yes" then rows 148 through 445 are hidden.

I've tried several variations but nothing seems to happen when I test the code, but selecting "Yes" from the dropdown in the cells shown.

Thank you in advance!

Answer 1

I don't understand what the second piece of code is expected to do. It does not resemble the code mostly used to allow multiple selections in a data validation drop-down.

Answer 2

You're right. The first code for hiding rows works, but the second code for mulitple selection from the dropdown does not. This is how it looks:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False 

Application.EnableEvents = False 

If Not Intersect(Range("B141"), Target) Is Nothing Then 

    Range("A292:A659").EntireRow.Hidden = (LCase(Range("B141").Value) = "yes") 

End If 

If Not Intersect(Range("B143"), Target) Is Nothing Then 

    Range("A148:A291,A446:A659").EntireRow.Hidden = (LCase(Range("B143").Value) = "yes") 

End If 

If Not Intersect(Range("B145"), Target) Is Nothing Then 

    Range("A148:A445").EntireRow.Hidden = (LCase(Range("B145").Value) = "yes") 

End If 

Application.EnableEvents = True 

Application.ScreenUpdating = True 

End Sub 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim cell As Range 

Dim oldVal As String 

Dim newVal As String 

On Error GoTo ErrHandler

If Target.Count > 1 Then Exit Sub 

Application.EnableEvents = False

Application.ScreenUpdating = False 

If Not Intersect(Target, Range("G300:G302")) Is Nothing Then '

    Set cell = Target.Cells(1) 

    oldVal = cell.Value 

If oldVal <> "" Then

        If InStr(1, cell.Value, ",") > 0 Then 

            newVal = InputBox("Selected options:", "Select options", cell.Value) 

            If newVal = "" Then 

                cell.ClearContents 

            Else 

                cell.Value = newVal 

            End If 

        End If 

    End If 

End If 

ErrHandler:

Application.EnableEvents = True 

Application.ScreenUpdating = True 

End Sub

Answer 3

Procedures such as Worksheet_Change and Worksheet_SelectionChange are not ordinary macros. They are so-called event procedures.

They should both be stored in the worksheet module, not in a standard module.

So you can start by cutting the code from Module2 and pasting it into the module of Sheet1.

But I suspect it won't do exactly what you want - let me know.

Answer 4

HansV,

Many thanks for your help. I started a new file with no VBA codes. I then copied the code you provided and it worked like a charm! However, I then copied a second code, which allows for multiple selections in the cell from the dropdowns within the sheet, but it's not working. It was working when I only had one VBA code. In the screenshot below you will see this code in the foreground. I selected the worksheet called "Sheet1 (Sheet1)" then I right clicked and selected INSERT then MODULE. Either I'm doing something wrong or the codes don't mix. Do you know what the issue could be? Thanks again!

Answer 5

Right-click the sheet tab.

Select View Code from the context menu.

Copy the following code into the worksheet module (not into a standard module!)

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Range("B141"), Target) Is Nothing Then
        Range("A292:A659").EntireRow.Hidden = (LCase(Range("B141").Value) = "yes")
    End If
    If Not Intersect(Range("B143"), Target) Is Nothing Then
        Range("A148:A291,A446:A659").EntireRow.Hidden = (LCase(Range("B143").Value) = "yes")
    End If
    If Not Intersect(Range("B146"), Target) Is Nothing Then
        Range("A148:A445").EntireRow.Hidden = (LCase(Range("B146").Value) = "yes")
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub