Hiding a SINGLE row from dropdown list.

isuckatexcel

New Member
Joined
May 21, 2019
Messages
4
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,789
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.
Code:
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
Code:
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

New Member
Joined
May 21, 2019
Messages
4
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,789
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

New Member
Joined
May 21, 2019
Messages
4
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
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top