Trigger Cells Code

I think I've managed to add a sample workbook now

Is there a reason that your current code makes rows 22:23 under project one visible when QFSDU is selected even though they're the BLIP rows? The others in your example seem to work in a predictable pattern, just not Project1.

BSB

Great spot! It must be an error I made when writing out the row numbers. It should follow the pattern of all other projects.

Thank you

Assuming the question I asked above is down to a typo rather than by design, try the code below and see if it does what you need.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        With Me
            .Rows("10:79").Hidden = False
            If Not Intersect(Target, .Range("F9:P9")) Is Nothing Then
                If Target.Offset(1) <> "" Then
                    rw = 10
                Else
                    rw = Target.End(xlDown).Row
                End If
                .Rows("10:79").Hidden = True
                Select Case Target.Value
                    Case "SUPERDRUG HOTSPOT":   .Rows(rw & ":" & rw + 5).Hidden = False
                    Case "SUPERDRUG FSDU":      .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case "SUPERDRUG QFSDU":     .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case "SUPERDRUG GE":        .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case "SUPERDRUG BLIP":      .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub

BSB

Ignore the above suggestion, I can see a problem with that one already. Didn't think it through properly, did I!?
I'll amend it real quick then repost.

BSB

BSB you are brilliant! Thank you so much!

I do have a couple of questions if you don't mind me picking your brain?

1. If I select 'Blip' in any of the drop downs, and then decide I no longer need that project so clear the contents of the cell in row 9, is there a way to make the rows hidden again, as at the moment they stay unhidden?

2. Is there a way to have 5 projects unhidden at once? So for instance, if Project 1 is an FSDU, and project 2 is a QFSDU, is there a way to unhide both the FSDU row in project 1, and the QFSDU rows in project 2?

Hope that makes sense!

Thank you so much

Psris

Question 1 I can add in.

Question 2 is the problem I could see with my fist suggestion and am fixing now.

BSB

This should solve question 2.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        With Me
            If Not Intersect(Target, .Range("F9:P9")) Is Nothing Then
                Select Case Target.Offset(-1).Value
                    Case "PROJECT 1": rw = 10
                    Case "PROJECT 2": rw = 24
                    Case "PROJECT 3": rw = 38
                    Case "PROJECT 4": rw = 52
                    Case "PROJECT 5": rw = 66
                End Select
                
                .Rows(rw & ":" & rw + 13).Hidden = True
                
                Select Case Target.Value
                    Case "SUPERDRUG HOTSPOT":   .Rows(rw & ":" & rw + 5).Hidden = False
                    Case "SUPERDRUG FSDU":      .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case "SUPERDRUG QFSDU":     .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case "SUPERDRUG GE":        .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case "SUPERDRUG BLIP":      .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub

I can solve Q1 too, but getting rushed out the door to take the family to dinner now so will have to revisit later if nobody else has provided a solution in the mean time.
Not difficult to do, just run out of time. SORRY!

BSB

Thank you so much! I really appreciate your help.

Enjoy the family dinner too

I updated BSB's code to include one line to unhide all the respective rows when the target cell is blank. It's work more consistently if the data source is updated to include a blank cell, i.e source =Sheet2!$A$2:$A$7

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        With Me
            If Not Intersect(Target, .Range("F9:P9")) Is Nothing Then
                Select Case Target.Offset(-1).Value
                    Case "PROJECT 1": rw = 10
                    Case "PROJECT 2": rw = 24
                    Case "PROJECT 3": rw = 38
                    Case "PROJECT 4": rw = 52
                    Case "PROJECT 5": rw = 66
                End Select
                
                .Rows(rw & ":" & rw + 13).Hidden = True
                
                Select Case Target.Value
                    Case "SUPERDRUG HOTSPOT":   .Rows(rw & ":" & rw + 5).Hidden = False
                    Case "SUPERDRUG FSDU":      .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case "SUPERDRUG QFSDU":     .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case "SUPERDRUG GE":        .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case "SUPERDRUG BLIP":      .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                    Case "":                    .Rows(rw & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Originally Posted by maniacb

I updated BSB's code to include one line to unhide all the respective rows when the target cell is blank. It's work more consistently if the data source is updated to include a blank cell, i.e source =Sheet2!$A$2:$A$7

@maniacb - Thanks for picking this up. You are indeed corrcect that it will not work without adding a blank cell to the drop down list. For others that may stumble across this in the future I'd like to explain why, as it will not be the case for all.

In the instance of the sample file above it is a problem because of "merged cells" (avoid them like the provebial plague ladies and gents!).
As the target in this case is a merged cell, when testing how many cells are being changed (If Target.Cells.Count = 1), when a user changes the value via the drop down list it will count as 1 cell, even though merged to 2 cells, however, if you change the value to nothing by hitting the delete key, Excel will count the target as 2 cells.

If you add the "blank" option to the dropdown list you can avoid this as it will be counted as 1 cell changing, but you cannot guarantee the user will do that every time. Personally if I wanted to clear a cell I would automatically hit Delete rather than use a drop down list.

@Paris050512 - If you need the code adjusted to account for the above user inconsistency, let me know and I'll rewrite the code to account for it.

BSB

Assuming you will be expanding the sheet for up to 10 promo sections with the same rows within each section, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer, pr As Integer, r As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        pr = Target.Row
        Select Case Cells(Target.Row - 2, 6).Value
                    Case "PROMO 1": r = 0
                    Case "PROMO 2": r = 74
                    Case "PROMO 3": r = 149
                    Case "PROMO 4": r = 224
                    Case "PROMO 5": r = 299
                    Case "PROMO 6": r = 374
                    Case "PROMO 7": r = 449
                    Case "PROMO 8": r = 524
                    Case "PROMO 9": r = 599
                    Case "PROMO 10": r = 674
                End Select
        With Me
            If Not Intersect(Target, .Range("F" & r + 9 & ":P" & r + 9)) Is Nothing Then
                Select Case Target.Offset(-1).Value
                    Case "PROJECT 1": rw = 10 + r
                    Case "PROJECT 2": rw = 24 + r
                    Case "PROJECT 3": rw = 38 + r
                    Case "PROJECT 4": rw = 52 + r
                    Case "PROJECT 5": rw = 66 + r
                End Select
                
                .Rows(rw & ":" & rw + 13).Hidden = True
                
                Select Case Target.Value
                    Case "SUPERDRUG HOTSPOT":   .Rows(rw & ":" & rw + 5).Hidden = False
                    Case "SUPERDRUG FSDU":      .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case "SUPERDRUG QFSDU":     .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case "SUPERDRUG GE":        .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case "SUPERDRUG BLIP":      .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                    Case "":                    .Rows(rw & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Thank you both so much, you have both been a great help!

I do have other questions, but understand I've taken a lot of your resource already, so don't feel obliged to help!

At the moment I have Promo 1 with drop downs for 'Superdrug Hotspot' 'Superdrug Blip' etc, however promo 2 will have different drop down options, i.e. 'Boots Hotspot' 'Boots FSDU' and then promo 3 with be a similar format with 'Tesco Hotspot' 'Tesco FSDU' etc, how can I adapt this code so that each promo line can have different drop downs, and therefore unhide the relevant lines required?

All projects 1 - 5 will have the same drop down list within each promo - I think I may be over complicating this, but there are so many variables!

Thank you!

Assuming the second word is consistent throughout your drop downs, try the following option.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer, pr As Integer, r As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        pr = Target.Row
        Select Case Cells(Target.Row - 2, 6).Value
                    Case "PROMO 1": r = 0
                    Case "PROMO 2": r = 74
                    Case "PROMO 3": r = 149
                    Case "PROMO 4": r = 224
                    Case "PROMO 5": r = 299
                    Case "PROMO 6": r = 374
                    Case "PROMO 7": r = 449
                    Case "PROMO 8": r = 524
                    Case "PROMO 9": r = 599
                    Case "PROMO 10": r = 674
                End Select
        With Me
            If Not Intersect(Target, .Range("F" & r + 9 & ":P" & r + 9)) Is Nothing Then
                Select Case Target.Offset(-1).Value
                    Case "PROJECT 1": rw = 10 + r
                    Case "PROJECT 2": rw = 24 + r
                    Case "PROJECT 3": rw = 38 + r
                    Case "PROJECT 4": rw = 52 + r
                    Case "PROJECT 5": rw = 66 + r
                End Select
                
                .Rows(rw & ":" & rw + 13).Hidden = True
                
                Select Case True
                    Case Target.Value Like "*HOTSPOT":  .Rows(rw & ":" & rw + 5).Hidden = False
                    Case Target.Value Like "*FSDU":     .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case Target.Value Like "*QFSDU":    .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case Target.Value Like "*GE":       .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case Target.Value Like "*BLIP":     .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                    Case "":                            .Rows(rw & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Thank you for coming back so quickly. The data isn't consistent between the promo's, so I think I'm asking perhaps something that is unachievable? (I'm not the expert!)

I have attached a sample workbook again which I think shows more in depth what I'm after. I think when you see it you will realise why I went with the 'trigger cells' code I initially had, but that wasn't a great solution.

Perhaps I need to look at simplifying the way the data is inputted rather than try to figure a way to make the existing sheet work - what do you think?

Thank you

It would probably be helpful if we could better understand what the file is used for as there could be a much easier way altogether.

For example, what are the staggered lists under each project header used for? Why are they laid out that way?
Perhaps it would be easier to have Excel stamp a list of values into the rows when the cell value changes rather than all this showing/hiding of rows.

BSB

Thanks BSB, I agree, there must be a better, easier way for this to work. I don't know how to show you exactly without showing confidential data that I can't put onto a public webpage.

Let me see if I can figure a way.

Thank you!

Here is one way to solve the issue if you want to continue with the original design approach.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer, pr As Integer, r As Integer, cl As Integer
    
    If Target.Cells.Count = 1 Then
        Application.ScreenUpdating = False
        a = Sheets("Sheet2").[A1].CurrentRegion
        a = Application.Transpose(a)
        pr = Target.Row
        Select Case Cells(Target.Row - 2, 6).Value
                    Case "PROMO 1": r = 0: cl = 1
                    Case "PROMO 2": r = 74: cl = 2
                    Case "PROMO 3": r = 149: cl = 3
                    Case "PROMO 4": r = 224: cl = 4
                    Case "PROMO 5": r = 299: cl = 5
                    Case "PROMO 6": r = 374: cl = 6
                    Case "PROMO 7": r = 449: cl = 7
                    Case "PROMO 8": r = 524: cl = 8
                    Case "PROMO 9": r = 599: cl = 9
                    Case "PROMO 10": r = 674: cl = 10
                End Select
        With Me
            If Not Intersect(Target, .Range("F" & r + 9 & ":P" & r + 9)) Is Nothing Then
                Select Case Target.Offset(-1).Value
                    Case "PROJECT 1": rw = 10 + r
                    Case "PROJECT 2": rw = 24 + r
                    Case "PROJECT 3": rw = 38 + r
                    Case "PROJECT 4": rw = 52 + r
                    Case "PROJECT 5": rw = 66 + r
                End Select
                
                .Rows(rw & ":" & rw + 13).Hidden = True
                
                Select Case Target.Value
                    Case a(cl, 2): .Rows(rw & ":" & rw + 5).Hidden = False
                    Case a(cl, 3):     .Rows(rw + 6 & ":" & rw + 7).Hidden = False
                    Case a(cl, 4):     .Rows(rw + 8 & ":" & rw + 9).Hidden = False
                    Case a(cl, 5):     .Rows(rw + 10 & ":" & rw + 11).Hidden = False
                    Case a(cl, 6):     .Rows(rw + 12 & ":" & rw + 13).Hidden = False
                    Case Else:         .Rows(rw & ":" & rw + 13).Hidden = False
                End Select
            End If
        End With
        Application.ScreenUpdating = True
    End If
End Sub