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).
Thanking you in advance!
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"
Thanking you in advance!
Senior Developer
CERTIFIED EXPERT
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
to
This would result in something shorter like:
And much better would be using a DSL-approach using simple helper functions. E.g.
Just name the helper methods and its parameters according to its real content..
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"
to
Range("D9:G9").FormulaR1C1 = "1"
Range("D6:G6").FormulaR1C1 = "0"
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"
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
Just name the helper methods and its parameters according to its real content..
Hard to guess your exact situation, but if the only differences are in the value of Sheets("INSERT").Range("E1
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
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
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
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:
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
We could have a procedure which takes an integer value and does the reverse of the GetButtonsValue function.
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
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
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
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
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
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.
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
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
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
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.
CERTIFIED EXPERT
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
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