How VB code can unhide/hide rows based on combobox selection?

**jgomez** · 08-23-2012, 11:10 AM

Good morning… I have the code you see below that works but I would like to add to it because the drop down box has multiple choices.
What I have works… I just don’t know how to add to it. This is what I would like for it to do:

If “RE” or “Both Non-RE & RE” are selected I would like rows 88:89 & 99:102 to be unhidden

If “NON-RE” is selected I would like for row 99 to be unhidden

I am trying to learn… just a slow process.

Formula:

'Collateral type'

Private Sub ComboBox5_Change()



Dim Rng As Range



Rows("91:102").Hidden = 1



If ComboBox5.Value <> "" And ComboBox5.Value <> "Unsecured" Then

    Set Rng = Range("A91:J102")

    Rng.Resize(1 * ComboBox5.Value + ComboBox5.Value - 1).EntireRow.Hidden = 0

End If

   

End Sub

**tlafferty** · 08-23-2012, 01:27 PM

Whenever you have several possible outcomes to test, you should consider using the Select Case branching and control structure.

Select Case ComboBox5.Value
     Case Is = ""
          'What to do when blank is chosen
     Case Is = "Unsecured"
          'What to do when Unsecured is chosen
     Case Is = "RE"
          'What to do when RE is chosen
     Case Is = "Both Non-RE & RE"
          'What to do when Both Non-RE & RE is chosen
     Case Else
          'What to do when the user makes any other choice
End Select

Insert your row hide/unhide procedure after the commented lines which begin with 'What to do when...

**jgomez** · 08-23-2012, 04:24 PM

This is what I did:

Formula:

Private Sub ComboBox5_Change()



Select Case ComboBox5.Value



Dim Rng As Range



       Case Is = ""

       'What to do when blank is chosen

       Rows("91:103").EntireRow.Hidden = True

       Case Is = "Unsecured"

       'What to do when Unsecured is chosen

       Rows("91:103").EntireRow.Hidden = True

       Case Is = "RE"

       'What to do when RE is chosen

       Rows ("88:89") And Rows("99:102").EntireRow.Hidden = False

       Case Is = "Both NON-RE & RE"

       'What to do when Both NON-RE & RE is chosen

       Rows ("88:89") And Rows("99:102").EntireRow.Hidden = False

     

End Select



End Sub

It works for the "" & when Unsecured are selected but not for the last two. I think because I want to unhide certain rows & not others... Not sure how to write that part.

**jgomez** · 08-24-2012, 01:53 PM

Anyone have a clue? Google is no help here... but tha'ts prob. because I don't know what to look for.

**tlafferty** · 08-28-2012, 06:53 PM

For rows to unhide:
Rows ("88:89").EntireRow.Hidden = False
Rows("99:102").EntireRow.Hidden = False

**jgomez** · 09-04-2012, 01:25 PM

Thank you tlafferty... i actually figured this out at home... just don't have an internet connection there & I've been overwhelmed with work that I didn't get to mark this thread solved. Thanks again! Learned a bit...