Hi Guys
Im after a little bit of guidance in some VBA coding. My skills are quite limited... I basically learn bits that i need to know and im struggling with this even after hunting the wonderful web.
Heres the setup.. I have a workbook... theres about 8 worksheets but only 3 are relevant for this. On the main tab “Worksheet 1” I have 5 drop down boxes (C9:C13) containing the same list of 150 products. For 2 of those products, I needed to be able to unhide and hide rows on the 3 sheets based on if either of those were selected. If Product 1 is selected in the Range then unhide the required rows, it if doesn't appear in the table, then hide. For each of the 2 products, if they do not appear in the C9:C13 range they should always be hidden.
After some work I managed to come up with the attached code. It all worked as it should. This was fine when I only had 2 products that had specific rows to be unhidden.. then I needed to add a 3rd and the code grew massively (the version I've shared). Now I need to add another and my brain told me that this just isnt the right way to make something easily scalable if I had to add in any other products with specific rows. On the code ive highlighted in BLUE the specific rows for Product 1, RED is Product 2 and GREEN is 3.
Dim ProductDetails(5) As String
ProductDetails(0) = Left(Cells(9, 3).Value, 6)
ProductDetails(1) = Left(Cells(10, 3).Value, 6)
ProductDetails(2) = Left(Cells(11, 3).Value, 6)
ProductDetails(3) = Left(Cells(12, 3).Value, 6)
ProductDetails(4) = Left(Cells(13, 3).Value, 6)
Dim VAR_VONEC As Boolean
VAR_VONEC = IsInArray("VONE-C", ProductDetails)
Dim VAR_VONEM As Boolean
VAR_VONEM = IsInArray("VONE-M", ProductDetails)
Dim VAR_IPVPN As Boolean
VAR_IPVPN = IsInArray("IPVPN", ProductDetails)
If VAR_VONEC And VAR_VONEM And VAR_IPVPN Then
Rows("61:61").EntireRow.Hidden = False
Rows("46:49").EntireRow.Hidden = False
Rows("59:60").EntireRow.Hidden = False
Rows("62:62").EntireRow.Hidden = False
Rows("71:88").EntireRow.Hidden = False
Rows("102:102").EntireRow.Hidden = False
Sheets("Billing").Rows("4:10").EntireRow.Hidden = False
Sheets("Billing").Rows("22:33").EntireRow.Hidden = False
Sheets("Technical").Rows("12:12").EntireRow.Hidden = False
Sheets("Technical").Rows("17:17").EntireRow.Hidden = False
Sheets("Technical").Rows("24:24").EntireRow.Hidden = False
Sheets("Technical").Rows("11:11").EntireRow.Hidden = False
Sheets("Technical").Rows("16:16").EntireRow.Hidden = False
Else
If VAR_VONEC And VAR_VONEM Then
Rows("61:61").EntireRow.Hidden = False
Rows("46:49").EntireRow.Hidden = False
Rows("59:60").EntireRow.Hidden = False
Rows("62:62").EntireRow.Hidden = False
Rows("71:88").EntireRow.Hidden = False
Rows("102:102").EntireRow.Hidden = False
Sheets("Billing").Rows("4:10").EntireRow.Hidden = False
Sheets("Billing").Rows("22:33").EntireRow.Hidden = False
Sheets("Technical").Rows("12:12").EntireRow.Hidden = False
Sheets("Technical").Rows("17:17").EntireRow.Hidden = False
Sheets("Technical").Rows("24:24").EntireRow.Hidden = False
Sheets("Technical").Rows("11:11").EntireRow.Hidden = True
Sheets("Technical").Rows("16:16").EntireRow.Hidden = True
Else
If VAR_VONEC And VAR_IPVPN Then
Rows("61:61").EntireRow.Hidden = True
Rows("46:49").EntireRow.Hidden = False
Rows("59:60").EntireRow.Hidden = False
Rows("62:62").EntireRow.Hidden = False
Rows("71:88").EntireRow.Hidden = False
Rows("102:102").EntireRow.Hidden = False
Sheets("Billing").Rows("4:10").EntireRow.Hidden = False
Sheets("Billing").Rows("22:33").EntireRow.Hidden = False
Sheets("Technical").Rows("12:12").EntireRow.Hidden = False
Sheets("Technical").Rows("17:17").EntireRow.Hidden = False
Sheets("Technical").Rows("24:24").EntireRow.Hidden = False
Sheets("Technical").Rows("11:11").EntireRow.Hidden = False
Sheets("Technical").Rows("16:16").EntireRow.Hidden = False
Else
If VAR_IPVPN And VAR_VONEM Then
Rows("61:61").EntireRow.Hidden = False
Rows("46:49").EntireRow.Hidden = True
Rows("59:60").EntireRow.Hidden = True
Rows("62:62").EntireRow.Hidden = True
Rows("71:88").EntireRow.Hidden = True
Rows("102:102").EntireRow.Hidden = True
Sheets("Billing").Rows("4:10").EntireRow.Hidden = True
Sheets("Billing").Rows("22:33").EntireRow.Hidden = True
Sheets("Technical").Rows("12:12").EntireRow.Hidden = True
Sheets("Technical").Rows("17:17").EntireRow.Hidden = True
Sheets("Technical").Rows("24:24").EntireRow.Hidden = True
Sheets("Technical").Rows("11:11").EntireRow.Hidden = False
Sheets("Technical").Rows("16:16").EntireRow.Hidden = False
Else
If VAR_VONEC Then
Rows("61:61").EntireRow.Hidden = True
Rows("46:49").EntireRow.Hidden = False
Rows("59:60").EntireRow.Hidden = False
Rows("62:62").EntireRow.Hidden = False
Rows("71:88").EntireRow.Hidden = False
Rows("102:102").EntireRow.Hidden = False
Sheets("Billing").Rows("4:10").EntireRow.Hidden = False
Sheets("Billing").Rows("22:33").EntireRow.Hidden = False
Sheets("Technical").Rows("12:12").EntireRow.Hidden = False
Sheets("Technical").Rows("17:17").EntireRow.Hidden = False
Sheets("Technical").Rows("24:24").EntireRow.Hidden = False
Sheets("Technical").Rows("11:11").EntireRow.Hidden = True
Sheets("Technical").Rows("16:16").EntireRow.Hidden = True
Else
If VAR_VONEM Then
Rows("61:61").EntireRow.Hidden = False
Rows("46:49").EntireRow.Hidden = True
Rows("59:60").EntireRow.Hidden = True
Rows("62:62").EntireRow.Hidden = True
Rows("71:88").EntireRow.Hidden = True
Rows("102:102").EntireRow.Hidden = True
Sheets("Billing").Rows("4:10").EntireRow.Hidden = True
Sheets("Billing").Rows("22:33").EntireRow.Hidden = True
Sheets("Technical").Rows("12:12").EntireRow.Hidden = True
Sheets("Technical").Rows("17:17").EntireRow.Hidden = True
Sheets("Technical").Rows("24:24").EntireRow.Hidden = True
Sheets("Technical").Rows("11:11").EntireRow.Hidden = True
Sheets("Technical").Rows("16:16").EntireRow.Hidden = True
Else
If VAR_IPVPN Then
Rows("61:61").EntireRow.Hidden = True
Rows("46:49").EntireRow.Hidden = True
Rows("59:60").EntireRow.Hidden = True
Rows("62:62").EntireRow.Hidden = True
Rows("71:88").EntireRow.Hidden = True
Rows("102:102").EntireRow.Hidden = True
Sheets("Billing").Rows("4:10").EntireRow.Hidden = True
Sheets("Billing").Rows("22:33").EntireRow.Hidden = True
Sheets("Technical").Rows("12:12").EntireRow.Hidden = True
Sheets("Technical").Rows("17:17").EntireRow.Hidden = True
Sheets("Technical").Rows("24:24").EntireRow.Hidden = True
Sheets("Technical").Rows("11:11").EntireRow.Hidden = False
Sheets("Technical").Rows("16:16").EntireRow.Hidden = False
Else
Rows("46:49").EntireRow.Hidden = True
Rows("59:62").EntireRow.Hidden = True
Rows("71:88").EntireRow.Hidden = True
Rows("102:102").EntireRow.Hidden = True
Sheets("Billing").Rows("4:10").EntireRow.Hidden = True
Sheets("Billing").Rows("22:33").EntireRow.Hidden = True
Sheets("Technical").Rows("12:12").EntireRow.Hidden = True
Sheets("Technical").Rows("17:17").EntireRow.Hidden = True
Sheets("Technical").Rows("24:24").EntireRow.Hidden = True
Sheets("Technical").Rows("11:11").EntireRow.Hidden = True
Sheets("Technical").Rows("16:16").EntireRow.Hidden = True
End If
End If
End If
End If
End If
End If
End If
Display More
So I changed tack completely and went at it from another direction. What I then tried is, on each of the 3 worksheets, in Column A.. on the specific rows i need to unhide.. I have a formula that either populates with “UH” if it appears in the Product selection and “H” if it does not. I then tried my best to write a worksheet change on “Worksheet 1” to go and find the “UH” in Column A and unhide it... if its “H” then it should be hidden. It also needs to take into the account the other 2 worksheets that it also needs to apply to (“Worksheet 2”), etc. I've got myself so confused. The only content in column A on any worksheet, are the UH/H formulas on the specific rows to be included. I tried many different things that either do nothing or give me the waiting circle and then do nothing.
So I guess this is a bit 2 fold... should i have stuck with my original idea but gone at the code in a different way (my skills are limited so i try to adapt what i know)... or was adding formulas to the rows in question and putting some code around that the best way to make this scalable?
Please let me know if any further info is needed, id rather not upload the workbook as i would have to give it a good sanitise, but if needed im sure i can do something.
Thanks in advance for any assistance for my brain.