VBA to hide cells based on dropdown list using If/Else/Then

**JOHNROK** · 07-30-2012, 12:47 PM

Hello,

Thanks for your time. I am currently trying to write a VBA that will hide a RANGE of cells based on the value of a drop down list. I.E., my dropdown list is in C18 and contains a list of 0-6 (number of dents). So, if the user selects say 2 dents, I want to hide cells A28:C43 (dent dimensions-an entire block of cells). I have a VBA written, but as of right now it is hiding the entire column, and I haven't figure out how to edit it for a range of cells and not just the column. Here is my code

Private Sub ComboBox1_Change()

 If Range("C18").Value = "0" Then
 Range("C19:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "1" Then
 Range("C19:C23").EntireColumn.Hidden = False
 Range("C23:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "2" Then
 Range("C19:C27").EntireColumn.Hidden = False
 Range("C28:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "3" Then
 Range("C19:C31").EntireColumn.Hidden = False
 Range("C32:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "4" Then
 Range("C19:C35").EntireColumn.Hidden = False
 Range("C36:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "5" Then
 Range("C19:C39").EntireColumn.Hidden = False
 Range("C40:C43").EntireColumn.Hidden = True
 ElseIf Range("C18").Value = "6" Then
 Range("C19:C43").EntireColumn.Hidden = False
 End If
 
 End Sub

If this is not enough information required, please let me know.
Any help will be much appreciated. Thanks!

---------- Post added at 10:47 AM ---------- Previous post was at 09:58 AM ----------

Is this even possible? When using Format>Visibility>Hide and Unhide, there is not option for hiding cells I know, but I'm hoping there's a way to put bounds on entirecolumn.hidden/entirerow.hidden.

Thanks again.

**Cutter** · 07-30-2012, 01:27 PM

@ JOHNROK

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**JOHNROK** · 07-30-2012, 06:13 PM

Ok, so after working through this all morning, I have found that hiding individual cells is impossible (to my knowledge), however, I changed the format of my sheet around so that hiding an entire ROW is ok. I have also changed for a dropdown list to a combo box. With this, I am now able to hide individual rows and it is working perfectly, except I would like for it to run everytime I change the value in the combobox, and not have to open the macro everytime I change that value. So, here is the code, please let me know how I can get the macro to run automatically.

Thanks again,

Sub DropDown1_Change()

 If Range("A48").Value = "0" Then
 Range("A12:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "1" Then
 Range("A12:A16").EntireRow.Hidden = False
 Range("A17:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "2" Then
 Range("A12:A20").EntireRow.Hidden = False
 Range("A21:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "3" Then
 Range("A12:A24").EntireRow.Hidden = False
 Range("A25:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "4" Then
 Range("A12:A28").EntireRow.Hidden = False
 Range("A29:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "5" Then
 Range("A12:A32").EntireRow.Hidden = False
 Range("A33:A36").EntireRow.Hidden = True
 ElseIf Range("A48").Value = "6" Then
 Range("A12:A36").EntireRow.Hidden = False
 
 End If
 End Sub

---------- Post added at 04:13 PM ---------- Previous post was at 04:05 PM ----------

I have solved it! So, the problem was I had writen the macro in VBA (ctrl+F11, right click sheet and view code), but I hadn't "assigned" the macro to the combo box by right clicking on the combobox and "assigning a macro". Hope this helps for anyone else with this question