Trying to hide rows based on a drop down yes no

    Quiet simply i am trying to hide columns and based on the value of a drop down (YES) box do i only then want them to become visible, i am open to all options, but if it is VB code it will have to be step by step as i am only knew to the coding.


    Thanks

    Re: Trying to hide rows based on a drop down yes no


    The following is the code that i have figured out will work, but how would i modify this that the information is hidden until the YES is entered;
    any ideas


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
    Application.EnableEvents = False
    If UCase(Target.Value) = "NO" Then
    Rows("3:5").Hidden = True
    Else
    Rows("3:5").Hidden = False
    End If

    Application.EnableEvents = True
    End If
    End Sub

    Re: Trying to hide rows based on a drop down yes no


    Code
    [COLOR=#333333][INDENT]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Rows("3:5").Hidden = uCase(Target.Value) <> "YES")
    End If


End Sub


    [/INDENT]
    [/COLOR]

    Re: Trying to hide rows based on a drop down yes no


    Hi Grimes0332,


    I have your code in and it is showing me a compile error, expected end of statement :(

    Re: Trying to hide rows based on a drop down yes no


    Hi Grimes0332,


    That works a treat, this is a very dumbo question to be asking but i am becoming a bit brave, firstly i also want Rows 25:28 and 31:33 to be also automatically hidden on entry to the form and i have 2 other cells with a drop down YES/NO (cell E22 and E23)
    So when Cell E22 = YES to show cells 25:28
    and when Cell E23 = NO to show cells 31:33.


    How would i put this into the code that you have given.

    Re: Trying to hide rows based on a drop down yes no


    Just copy the 3 lines and change for the different ranges and rows to hide. You'll have to experiment with the '<> "YES"'.


    It won't be the most efficient code, but if you get it working you can then try experimenting with a 'Select Case' structure to improve it.

    Re: Trying to hide rows based on a drop down yes no


    This works a treat Grimes0332,


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$22" Then
    Rows("25:28").Hidden = UCase(Target.Value) <> "YES"
    End If

    If Target.Address = "$E$23" Then
    Rows("31:33").Hidden = UCase(Target.Value) <> "NO"
    End If

    If Target.Address = "$D$49" Then
    Rows("50:52").Hidden = UCase(Target.Value) <> "YES"
    End If

    End Sub



    Just one last question is it possible to have the cell saying please select" YES/NO" or just YES/NO in the cell keeping the cells hidden until the YES/NO is selected.

    Re: Trying to hide rows based on a drop down yes no


    Nothing wrong with that but you can tidy it up a little. Sometimes people like to see the most 'efficient' code


    More efficient because instead of checking 3 conditions every time, it will only check until it finds a True condition, show/hide those rows and then exit.


    I don't understand your last comment.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login