Results 1 to 2 of 2

Thread: Hiding rows based on a cell value with multiple values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hiding rows based on a cell value with multiple values

    Hey everyone,
    I'm trying to write a code that hides rows based on cell value, but with a twist. The cell that it is based off of is a drop down. I have it to where you can select multiple options within the cell. I have a list of colors in the drop down and at the end I have a "Special Color" option. I need the "Special Color" option to appear only when selected. The drop down list is in cell B2 and the Special color is going to be written in B3. The entire row can be hidden. Here is the code I am using for being able to select multiple options in the drop down.

    Dim Oldvalue As String
    Dim Newvalue As String

    On Error GoTo Exitsub
    If Target.Address = "$B$2" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = "" Then
    Target.Value = Newvalue
    Else
    Target.Value = Oldvalue & ", " & Newvalue
    End If
    End If
    End If

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hiding rows based on a cell value with multiple values

    Hi ERed1. Please when you post code, use code tags around your code (see example in red below)

    I don't understand what your code is doing or trying to achieve. Where is it residing? Is it in a worksheet module, worksheet_change() perhaps?

    There is also a bug in this code: you are setting EnableEvents to False, which switches this off. But you don't reset it to True later on. So your code will only work once. After that Excel won't react to events! This is a 'sticky' property. You always need to reset EnableEvents.

    And why are you calling the Undo method?

    Furthermore I don't understand your question.
    You have a listbox with colours listed (say red, blue and green)
    Then there could be another item added 'Special Color'.
    You want this to appear 'only if selected'. What needs to be selected? You can't select it if it isn't there in the first place
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •