VBA - multiple options affecting IF

I have various choices on a worksheet named "INSERT" that hide rows on a worksheet called "Calculation".
The worksheet "INSERT" is hidden before users can change selections in "Calculation".
Some of these changes are overriding selections in "INSERT", (i.e. some rows that were hidden).

I need to do some sort of IF AND statement but there are 6 options in "INSERT" and therefore my code may end up being very long, unless you could kindly assist me please.

A selection of an idea I have is as follows:

I should also state that within the code for the macros when clicking the buttons within "INSERT" to make selections, I have entered a 1 in a cell underneath the button to show that it was selected (e.g. E19).
 ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
       Sheets("Calculation").Unprotect Password:="hello01"
        Range("D9:G9").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("D6:G6").Select
        ActiveCell.FormulaR1C1 = "0"
        Rows("6:6").Hidden = True
        Rows("9:10").Hidden = False
        Rows("11:11").Hidden = False
        Rows("13:13").Hidden = True
        Rows("22:22").Hidden = False
        Rows("32:34").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
       Sheets("Calculation").Unprotect Password:="hello01"
        Range("D9:G9").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("D6:G6").Select
        ActiveCell.FormulaR1C1 = "0"
        Rows("6:6").Hidden = False
        Rows("9:10").Hidden = False
        Rows("11:11").Hidden = False
        Rows("13:13").Hidden = True
        Rows("22:22").Hidden = False
        Rows("32:34").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"

Open in new window

The above code is for only 1 of the 6 choices so you can see why it'd end up being very long without a shortcut you may know of.
Thanking you in advance!
James CAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Hard to tell, but you have some redundancy in your code. So why not setting the default befor the If-block and then only setting the differences in the Then-branches?

Also place the Unprotect and Protect before and after your If-block. Then you only need each call once.

And use direct manipulation of objects where possible. Reduce

Range("D9:G9").Select
ActiveCell.FormulaR1C1 = "1"
Range("D6:G6").Select
ActiveCell.FormulaR1C1 = "0"

Open in new window


to

Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").FormulaR1C1 = "0"

Open in new window


This would result in something shorter like:

Sheets("Calculation").Unprotect Password:="hello01"   
  
If InitialCondition Then

ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
	Range("D9:G9").FormulaR1C1 = "1"
	Range("D6:G6").FormulaR1C1 = "0"
	Rows("6:6").Hidden = True
	Rows("9:10").Hidden = False
	Rows("11:11").Hidden = False
	Rows("13:13").Hidden = True
	Rows("22:22").Hidden = False
	Rows("32:34").Hidden = True
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
	Range("D9:G9").FormulaR1C1 = "1"
	Range("D6:G6").FormulaR1C1 = "0"
	Rows("6:6").Hidden = False
	Rows("9:10").Hidden = False
	Rows("11:11").Hidden = False
	Rows("13:13").Hidden = True
	Rows("22:22").Hidden = False
	Rows("32:34").Hidden = True
EndIf   

Sheets("Calculation").Protect Password:="hello01"

Open in new window


And much better would be using a DSL-approach using simple helper functions. E.g.

Public Sub SetValues
	
	UnprotectSheet  
	
	If InitialCondition Then

	ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
		SetFormula "1", "0"
		SetHidden True, False, False, True, False, True
	ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
		SetFormula "1", "0"
		SetHidden False, False, False, True, False, True
	EndIf   
	   
	ProtectSheet
	 
End Sub

Private Sub ProtectSheet()
  
  Sheets("Calculation").Protect Password:="hello01"
  
End Sub

Private Sub UnprotectSheet()

  Sheets("Calculation").Unprotect Password:="hello01"   

End Sub

Private Sub SetFormula(F1 As String, F2 As String)

	Range("D9:G9").FormulaR1C1 = F1
	Range("D6:G6").FormulaR1C1 = F2

End Sub

Private Sub SetHidden(R1 As Boolen, R2 As Boolen, R3 As Boolen, R4 As Boolen, R5 As Boolen, R6 As Boolen)

	Rows("6:6").Hidden = R1
	Rows("9:10").Hidden = R2
	Rows("11:11").Hidden = R3
	Rows("13:13").Hidden = R4
	Rows("22:22").Hidden = R5
	Rows("32:34").Hidden = R6
		
End Sub		

Open in new window


Just name the helper methods and its parameters according to its real content..
1
GrahamSkanRetiredCommented:
Hard to guess your exact situation, but if the only differences are in the value of  Sheets("INSERT").Range("E19"), and you need six such values then you could do something like
If Range("D3").Value = "L" Then
    Select Case Sheets("INSERT").Range("E19")
        Case 0 To 5
            Sheets("Calculation").Unprotect Password:="hello01"
             Range("D9:G9").Select
             ActiveCell.FormulaR1C1 = "1"
             Range("D6:G6").Select
             ActiveCell.FormulaR1C1 = "0"
             Rows("6:6").Hidden = False
             Rows("9:10").Hidden = False
             Rows("11:11").Hidden = False
             Rows("13:13").Hidden = True
             Rows("22:22").Hidden = False
             Rows("32:34").Hidden = True
            Sheets("Calculation").Protect Password:="hello01"
    End Select
End If

Open in new window

0
James CAuthor Commented:
Sorry, there's a lot of code and I tried to strip out the relevant part.

I have copied the relevant section into the attached document.

Buttons on 'INSERT' will change the corresponding cell in row 19.
Then this sheet will be hidden, but I want the choices in this sheet to be maintained.

The way I see it is that there are 6 choices, but therefore 36 combinations of the different choices.

Thanks ste5an but I don't know enough VBA for this to make total sense to me.  
Thanks GrahamSkan - hopefully the attached and above will help explain my predicament.
TestJC.xlsm
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GrahamSkanRetiredCommented:
The documents helps a bit in understanding.

In summary:
You have six cells in row 19 of the "INSERT" sheet that can have a value of 0 or 1. Your code gives an example of what to do depending on the value of one of the cells. The code for each choice is a bit over 20 lines long. These two pieces of code are very similar.
One way to approach it would be derive a single number from the choices and to write a single Select Case/End Select statement with a Case block for each value. Unfortunately the number of possible values (64) is even higher that you thought.
 
Here is some code to derive the value:
Function GetButtonsValue() As Integer
    Dim i As Integer 'power
    Dim c As Integer 'column
    Dim v As Integer 'cumulative value
    
    With Sheets("INSERT")
        For c = 2 To 7
        i = c - 2
        If .Cells(19, c) = 1 Then
            v = v + 2 ^ i
        End If
        Next c
    End With
    GetButtonsValue = v
End Function

Open in new window


We don't know how the values interact, or how much duplication there is in the desired code for each block. Code that is common to many choices could be put in a separate procedure. I think that might be the same as or a similar point to what ste5an is referring to. (I'm not sure what DSL is).

In the case of code like this
        Rows("6:6").Hidden = True
        Rows("9:9").Hidden = False
        Rows("10:10").Hidden = True
        Rows("11:11").Hidden = False
        Rows("13:13").Hidden = True
        Rows("22:27").Hidden = False
        Rows("32:34").Hidden = True

Open in new window


We could have a procedure which takes an integer value and does the reverse of the GetButtonsValue function.

Sub SetHiddenRows(iHidden As Integer)
    Rows("6:6").Hidden = 1 And iHidden
    Rows("9:9").Hidden = 2 And iHidden
    Rows("10:10").Hidden = 4 And iHidden
    Rows("11:11").Hidden = 8 And iHidden
    Rows("13:13").Hidden = 16 And iHidden
    Rows("22:27").Hidden = 32 And iHidden
    Rows("32:34").Hidden = 64 And iHidden
End Sub

Open in new window

1
James CAuthor Commented:
I have uploaded the commonalities and differences so it's really clear.
In the table on the right is all the rows that are to be hidden depending on selections in 'Calculation' cell D3.
The table on the left shows those rows that should hidden by the buttons but NOT be overwritten by changing selections in D3.
Code1.xlsx
TestJC.xlsm
0
GrahamSkanRetiredCommented:
Sorry about the delay. Personal things have taken my time in the last few days.

It isn't clear whether any or all the target rows are to be unhidden by default. If necessary they could be all unhidden at the start of the process. The code is untested.
Sub HideRows()
    Dim LRows() As Variant
    Dim HPRows() As Variant
    Dim LoRows() As Variant
    Dim HideRows() As Variant
    Dim c As Integer
    
    HideRows = Array(9, 11, 12, 10, 31, 13) 'list of target rows (less 34 to 36)
    LRows = Array(1, 1, 1, 1, 1, 0)
    HPRows = Array(1, 0, 1, 0, 1, 1)
    LoRows = Array(0, 0, 1, 0, 1, 1)
    
    Select Case Range("D3").Value
        Case "L"
            Range("D9:G9").FormulaR1C1 = "0"
            Range("D6:G6").FormulaR1C1 = "0"
            Rows("6:6").Hidden = True
            Rows("13:13").Hidden = True
            Rows("32:33").Hidden = True
            For c = 0 To 5
                If LRows(c) = 1 Then
                    If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
                        Rows(HideRows(c)).Hidden = True
                        If c = 3 Then
                            Rows("34:36").Hidden = True 'special for L,E19
                        End If
                    End If
                End If
            Next c
            
        Case "HP"
            Range("D9:G9").FormulaR1C1 = "1"
            Range("D6:G6").FormulaR1C1 = "0"
            dws.Rows("10:11").Hidden = True
            dws.Rows("26:27").Hidden = True
            dws.Rows("34:36").Hidden = True
            For c = 0 To 5
                If HPRows(c) = 1 Then
                    If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
                        Rows(HideRows(c)).Hidden = True
                    End If
                End If
            Next c
    
        Case "Lo"
            Range("D9:G9").FormulaR1C1 = "0"
            Rows("6:6").Hidden = True
            Rows("9:11").Hidden = True
            Rows("13:13").Hidden = True
            Rows("22:22").Hidden = True
            Rows("26:27").Hidden = True
            Rows("33:36").Hidden = True
            For c = 0 To 5
                If LoRows(c) = 1 Then
                    If Sheets("INSERT").Range.Cells(19, c + 2) = "1" Then
                        Rows(HideRows(c)).Hidden = True
                    End If
                End If
            Next c
    End Select
End Sub

Open in new window

0
James CAuthor Commented:
No problem Graham, hope everything's ok.

The rows are solely dependant on the selections in the code above so no default as such.

I have deciphered what your code is trying to achieve, not having any prior understanding of arrays.  I've copied and pasted it into the test file, and changing cell D3 has no effect on hiding any rows, regardless of any button selection in the 'Insert' sheet.

As I said, I can't see what bit of code is preventing this from working, as I'm very new to this. Your help is appreciated.

I've attached the file with the code in.

Thanks.
TestJC.xlsm
0
GrahamSkanRetiredCommented:
Sorry, I can't test it. The code fails at the first  attempt to hide a row. I think it is because the sheet is protected.
1
James CAuthor Commented:
Sorry, now with unprotect/protect code.
TestJC.xlsm
0
GrahamSkanRetiredCommented:
Sorry, I lost track of this question.

I have stepped through this code and eliminated the syntax errors.
I have guessed that 'dws' is a sheet object, so I set up a blank sheet with that name.
Sub HideRows()
Sheets("INSERT").Unprotect Password:="hello01"
Sheets("Calculation").Unprotect Password:="hello01"
Sheets("Calculation").Select
    Dim LRows() As Variant
    Dim HPRows() As Variant
    Dim LoRows() As Variant
    Dim HideRows() As Variant
    Dim c As Integer
    Dim dws As Worksheet
    Set dws = Sheets("dws")
    HideRows = Array(9, 11, 12, 10, 31, 13) 'list of target rows (less 34 to 36)
    LRows = Array(1, 1, 1, 1, 1, 0)
    HPRows = Array(1, 0, 1, 0, 1, 1)
    LoRows = Array(0, 0, 1, 0, 1, 1)
    
    Select Case Range("D3").Value
        Case "L"
            Range("D9:G9").FormulaR1C1 = "0"
            Range("D6:G6").FormulaR1C1 = "0"
            Rows("6:6").Hidden = True
            Rows("13:13").Hidden = True
            Rows("32:33").Hidden = True
            For c = 0 To 5
                If LRows(c) = 1 Then
                    If Sheets("INSERT").Cells(19, c + 2) = "1" Then
                        Rows(HideRows(c)).Hidden = True
                        If c = 3 Then
                            Rows("34:36").Hidden = True 'special for L,E19
                        End If
                    End If
                End If
            Next c
            
        Case "HP"
            Range("D9:G9").FormulaR1C1 = "1"
            Range("D6:G6").FormulaR1C1 = "0"
            dws.Rows("10:11").Hidden = True
            dws.Rows("26:27").Hidden = True
            dws.Rows("34:36").Hidden = True
            For c = 0 To 5
                If HPRows(c) = 1 Then
                    If Sheets("INSERT").Cells(19, c + 2).Value = "1" Then
                        Rows(HideRows(c)).Hidden = True
                    End If
                End If
            Next c
    
        Case "Lo"
            Range("D9:G9").FormulaR1C1 = "0"
            Rows("6:6").Hidden = True
            Rows("9:11").Hidden = True
            Rows("13:13").Hidden = True
            Rows("22:22").Hidden = True
            Rows("26:27").Hidden = True
            Rows("33:36").Hidden = True
            For c = 0 To 5
                If LoRows(c) = 1 Then
                    If Sheets("INSERT").Cells(19, c + 2) = "1" Then
                        Rows(HideRows(c)).Hidden = True
                    End If
                End If
            Next c
    End Select

    Sheets("INSERT").Protect Password:="hello01"
    Sheets("Calculation").Protect Password:="hello01"

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: GrahamSkan (https:#a42081454)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.