Forum Discussion

chanyuhui :'s avatar
chanyuhui :
Copper Contributor
Apr 26, 2018

Excel - automatically hiding select rows based on drop down selection

Hi there! I have a drop down selection of 10 items on my dashboard tab and am trying to only show rows 57 to 72 when I select one of them. I am using the following code but somehow it is not working...
Formulas & Functions
Need Help
RishiSumit's avatar
RishiSumit
Copper Contributor
Nov 02, 2020

Haytham Amairah 

 

I am looking to create a worksheet where a user is presented with different options (more than 2 options) from a dropdown and based on their responses (i.e if user chooses any one of 2 options - eg. choice A or choice B), a new question appears on the Excelsheet in a row below the first question, otherwise they don't.

 

And then based on the users' response to this second question (posed as a dropdown choice), a third question appears or does not appear.

 

As is obvious, I am seeking to have several nested questions within the Excelsheet where each subsequent question shows up as a Dropdown based on the user's response from a previous question (also a dropdown). 

 

I am able to create dropdowns for each question and create one of the questions to appear based on a prior question, but am having trouble going any further.

 

Any help would be much appreciated. Thank you!

  • RishiSumit's avatar
    RishiSumit
    Copper Contributor
    Nov 02, 2020
    Thanks Sergei.

    This video shows how to draw data from a dropdown list that is visible on the Excel sheet regardless of the user's response.

    However, I am trying to make a sheet where a follow up question (& its associated dropdown list) does not become visible unless user chooses either say option C or D (out of 6 options a,b,c,d,e,f). Once user chooses option c or d --> then the next question (& its dropdown list) appears in the following row.

    Thank you,
    • Jerry_Reddick's avatar
      Jerry_Reddick
      Copper Contributor
      Nov 14, 2020

      RishiSumit This is exactly what I am trying to do.  Did you find a resolution? I originally created this structure:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Select Hyper-V
      If Range("B26") = "Hyper-V" Then
      Rows("28:29").EntireRow.Hidden = False
      Else
      Rows("27").EntireRow.Hidden = True
      End If

      'Select VMware
      If Range("B26") = "VMware" Then
      Rows("27").EntireRow.Hidden = False
      Else
      Rows("28:29").EntireRow.Hidden = True
      End If
      End Sub

       

      It worked once or twice then it stopped working. Now what is happening is I can choose VMware from the dropdown and it shows and hides the correct lines. However, when I change the field to Hyper-V, it does not remove the VMware rows and replace them with the Hyper-V rows.

       

      Thanks,

      Jerry

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Nov 14, 2020

        Jerry_Reddick Try this:

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("B26")

Case "Hyper-V"
    Rows("28:29").EntireRow.Hidden = False
    Rows("27").EntireRow.Hidden = True

Case "VMware"
    Rows("27").EntireRow.Hidden = False
    Rows("28:29").EntireRow.Hidden = True

End Select

End Sub

         

Resources