Hiding a SINGLE row from dropdown list.

[isuckatexcel]

Hello,
I've done a lot of googling and can only find references to hiding multiple rows, or based off a fixed cell..

I have a buildsheet that I'm trying to hide a row based off a dropdown list, an example below:


Project Management >Included
>Excluded
>N/A

Site Supervisor >Included
>Excluded
>N/A


Each row corresponds to a different item that may or may not be used for that job.
I want to be able to go down the list of items and select a dropdown option, and upon selecting N/A, that particular row is hidden only.

Is this possible? Everything I have found references fixed cells and I don't know enough to adjust to my needs.
My dropdown list references P8:P10 and the dropdown is applied to column C.

Additionally, I would also like to grey out and lock a row based on a selection of Excluded. I have worked out how to grey it out but I cant see a way around locking the row without protecting the worksheet (I don't want to do this as some rows need to be custom filled).

Thanks in advance.

 

[isuckatexcel]

*bump

Anybody able to help me out?

 

[mikerickson]

To give you some help, there are a lot of assumptions that have to be made about the lay-out and such like.

I am assuming that you have "Project Management" in A1, "Site Supervisor" in A2 and the B1 and B2 have Validation Lists where the user will choose Included, Excluded or N/A

I am assuming that the Project Management choice will effect row 10 of the same sheet, and the Site Supervisor choice will effect row 15.
Included will make the row visible with a white background
Excluded will make the row visible with a grey background
N/A will hide the row.

With those assumptions, putting this code in the sheet's code module should do that.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRow As Long
    With Target
        If Not Application.Intersect(Range("B1:B2"), .Cells) Is Nothing Then
            If .Cells.Count <> 1 Then
                Beep
                MsgBox "don't paste into these cells"
                Application.Undo
            Else
                If .Row = 1 Then
                    keyRow = 10
                ElseIf .Row = 2 Then
                    keyRow = 15
                End If
                
                Select Case .Value
                    Case "Included"
                        Cells(keyRow, 1).EntireRow.Hidden = False
                        Cells(keyRow, 1).EntireRow.Interior.ColorIndex = xlNone
                    Case "Excluded"
                        Cells(keyRow, 1).EntireRow.Hidden = False
                        Cells(keyRow, 1).EntireRow.Interior.Color = RGB(208, 208, 208)
                    Case "N/A"
                        Cells(keyRow, 1).EntireRow.Hidden = True
                        Cells(keyRow, 1).EntireRow.Interior.ColorIndex = xlNone
                End Select
            End If
        End If
    End With
End Sub

If you want the user to also be unable to select cells in those rows when greyed out, put this also in that module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Rows.Count = 1 Then
            If .Row = 10 Or .Row = 15 Then
                If .Cells(1, 1).DisplayFormat.Interior.Color <> vbWhite Then
                    Application.EnableEvents = False
                    Beep
                    .Offset(1, 0).Select
                End If
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[isuckatexcel]

To give you some help, there are a lot of assumptions that have to be made about the lay-out and such like.

Thanks for your help Mike.
To clarify, Site supervisor is on one row. Labour is on the next row. I was trying to indicate the dropdown options with the >.

I have attached a section of the workbook to show you how it's laid out.
https://www.dropbox.com/s/yztf4kg0gpoiyy5/Example.xlsx?dl=0

Sorry, cant work out how to add a file on here..

 

[mikerickson]

I don't download linked files.

Did the code that I posted help you, were you able to adapt it to your lay-out?

 

[isuckatexcel]

I don't download linked files.

Did the code that I posted help you, were you able to adapt it to your lay-out?

Hi Mike,
Sorry about the delay in getting back to you.
I'm unsure how to edit it to get to what I need..

I can see that I need to adjust the ranges from B to C for my dropdown list..

But I can't see how I adjust the range for the rows being hidden?
Say if Site Supervision is row 37, I want to it to be greyed out and ideally locked if I select EXCLUDED, and hidden and locked if I select N/A.
Same again for Labour, but only for row 38..

Does that make sense?

Cheers