problem with code, if statement

S

Sian1

Board Regular
Joined
Nov 9, 2009
Messages
90
Hi, hope someone could help me with my code. When workbook first open, all fields are blank, the user be able to have selection options in H10 (Tier1, Tier 2, Tier 3, Tier 4).
let's say the user first select Tier 1 in H10, they are also have 3 options to be chosen in Program Type H13. however, if the user choose Tier 3 or 4, i set default to RT - No Insurance, so far, it is working fine. However, if the user change their mind and would like to switch H10 to either Tier 1 or 2 the default will remain in H13.
my question, how can i make the H13 to clear when the user decided to chose either Tier 1 or 2 from Tier 3 or 4 and be able also have the options to choose in the drop down list (3 options).
I was able to set H13 to clear the content, however whatever they selected in H13 will also be cleared out.

Hope someone could help asap!

1628180333710.png



VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Static a As Boolean
    Dim myPassword As String
    myPassword = "xxxxxxx"
    ActiveSheet.Unprotect Password:="xxxxxxxxx"
    'MsgBox a
    'MsgBox "start"
'Tier 1 No Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - No Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Tier 1 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
        Rows("39:45").EntireRow.Hidden = False
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Range("H13").Value = ""
'Tier 1 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance" Then
        Rows("39:45").EntireRow.Hidden = True
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Range("H13").Value = ""
'Tier 2 No Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - No Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Tier 2 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
        Rows("39:45").EntireRow.Hidden = False
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Range("H13").Value = ""
'Tier 2 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Teir 3/4'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 3" And a = False Then
        a = True
        Range("H28:H29").Locked = True
        Range("K10:l23").Locked = True
        Range("K25:l26").Locked = True
        Range("K24:L24").Locked = True
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = "RT - No Reinsurance"
        'Range("H13").Locked = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 4" And a = False Then
        a = True
        Range("H28:H29").Locked = True
        Range("K10:l23").Locked = True
        Range("K25:l26").Locked = True
        Range("K24:L24").Locked = True
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = "RT - No Reinsurance"
        'Range("H13").Locked = False
End If
 
Last edited by a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Try this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myPassword As String
  myPassword = "xxxxxxx"
  ActiveSheet.Unprotect Password:=myPassword
  If Target.Address(0, 0) <> "H10" Then Exit Sub
  
  'Tier 1 No Re'''''''''''''''''''''''''''''''''''''''
  If Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - No Reinsurance" Then
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = True
    Rows("39:45").EntireRow.Hidden = True
    Range("H13").Value = ""
  'Tier 1 With Re'''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
    Rows("39:45").EntireRow.Hidden = False
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = True
    Range("H13").Value = ""
  'Tier 1 With Re'''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance" Then
    Rows("39:45").EntireRow.Hidden = True
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = False
    Range("H13").Value = ""
  'Tier 2 No Re'''''''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - No Reinsurance" Then
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = True
    Rows("39:45").EntireRow.Hidden = True
    Range("H13").Value = ""
  'Tier 2 With Re''''''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
    Rows("39:45").EntireRow.Hidden = False
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = False
    Range("H13").Value = ""
  'Tier 2 With Re''''''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance" Then
    Range("H28:H29").Locked = False
    Range("K10:l23").Locked = False
    Range("K25:l26").Locked = False
    Range("H13").Locked = False
    Range("K24:L24").Locked = False
    Rows("39:45").EntireRow.Hidden = True
    Range("H13").Value = ""
  'Teir 3/4'''''''''''''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 3" Then
    Range("H28:H29").Locked = True
    Range("K10:l23").Locked = True
    Range("K25:l26").Locked = True
    Range("K24:L24").Locked = True
    Rows("39:45").EntireRow.Hidden = True
    Range("H13").Locked = False
    Range("H13").Value = "RT - No Reinsurance"
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ElseIf Range("H10").Value = "Tier 4" Then
    Range("H28:H29").Locked = True
    Range("K10:l23").Locked = True
    Range("K25:l26").Locked = True
    Range("K24:L24").Locked = True
    Rows("39:45").EntireRow.Hidden = True
    Range("H13").Locked = False
    Range("H13").Value = "RT - No Reinsurance"
  End If
  
  ActiveSheet.Protect Password:=myPassword
End Sub
 
Upvote 0
I took a closer look at the actions in your code and it boils down to the following cells:
VBA Code: 
    Rows("39:45").EntireRow.Hidden = 
    Range("H28:H29").Locked = 
    Range("K10:l23").Locked = 
    Range("K25:l26").Locked = 
    Range("H13").Locked = 
    Range("K24:L24").Locked = 
    Range("H13").Value =

And it can be summarized in these lines:
VBA Code: 
  Rows("39:45").EntireRow.Hidden = 
  Range("H28:H29, K10:L23, K25:L26").Locked = 
  Range("K24:L24").Locked = 
  Range("H13").Value =

_____________________________________________
The code can be reduced to the following:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myPassword As String
  myPassword = "xxxxxxx"
  ActiveSheet.Unprotect Password:=myPassword
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address(0, 0) <> "H10" Then Exit Sub
  
  Select Case Range("H10").Value
    'Tier 1 No Re'''''''''''''''''''''''''''''''''''''''
    Case "Tier 1"
      Select Case Range("H13").Value
        Case "RT - No Reinsurance"
          Call actions(True, False, True, "")
        Case "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)"
          Call actions(False, False, True, "")
        Case "RT - With Reinsurance"
          Call actions(True, False, False, "")
      End Select
    'Tier 2 No Re'''''''''''''''''''''''''''''''''''''''
    Case "Tier 2"
      Select Case Range("H13").Value
        Case "RT - No Reinsurance"
          Call actions(True, False, True, "")
        Case "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)"
          Call actions(False, False, False, "")
        Case "RT - With Reinsurance"
          Call actions(True, False, False, "")
      End Select
    'Teir 3/4'''''''''''''''''''''''''''''''''''''''''''''''
    Case "Tier 3", "Tier 4"
      Call actions(True, True, True, "RT - No Reinsurance")
  End Select
  
  ActiveSheet.Protect Password:=myPassword
End Sub

Sub actions(xRows As Boolean, xRng1 As Boolean, xRng2 As Boolean, xValue As String)
  Rows("39:45").EntireRow.Hidden = xRows
  Range("H28:H29, K10:L23, K25:L26").Locked = xRng1
  Range("K24:L24").Locked = xRng2
  Range("H13").Value = xValue
End Sub
 
Upvote 0
I think the code can be shortened a bit more and in this way be clearer.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myPassword As String, tr1 As String, tr2 As String, tr3 As String
  
  myPassword = "xxxxxxx"
  ActiveSheet.Unprotect Password:=myPassword
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address(0, 0) <> "H10" Then Exit Sub
  
  tr1 = "RT - No Reinsurance"
  tr2 = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)"
  tr3 = "RT - With Reinsurance"

  Select Case Range("H10").Value
    Case "Tier 1"
      If Range("H13").Value = tr1 Then Call actions(True, False, True, "")
      If Range("H13").Value = tr2 Then Call actions(False, False, True, "")
      If Range("H13").Value = tr3 Then Call actions(True, False, False, "")
    Case "Tier 2"
      If Range("H13").Value = tr1 Then Call actions(True, False, True, "")
      If Range("H13").Value = tr2 Then Call actions(False, False, False, "")
      If Range("H13").Value = tr3 Then Call actions(True, False, False, "")
    Case "Tier 3", "Tier 4":           Call actions(True, True, True, "RT - No Reinsurance")
  End Select
  
  ActiveSheet.Protect Password:=myPassword
End Sub

Sub actions(xRows As Boolean, xRng1 As Boolean, xRng2 As Boolean, xValue As String)
  Rows("39:45").EntireRow.Hidden = xRows
  Range("H28:H29, K10:L23, K25:L26").Locked = xRng1
  Range("K24:L24").Locked = xRng2
  Range("H13").Value = xValue
End Sub
 
Upvote 0
Thanks DanteAmor, however the Rows("39:45").EntireRow.Hidden = True or Rows("39:45").EntireRow.Hidden = False doesn't work as expected,
So, when "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" is chosen, the entire rows 39:45 should be shown else hidden. Do you think you could help?
 
Upvote 0
Let's see if I understand your request. You want to modify cell H10 and have some events occur in the cells and in the rows, but you also want to modify cell H13 and have events occur in the rows and cells.

Try the following. If it is not what you need, then you will have to explain for each combination of H10 and H13 what should happen in the rows and in the cells.


VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myPassword As String, tr1 As String, tr2 As String, tr3 As String
  
  myPassword = "xxxxxxx"
  ActiveSheet.Unprotect Password:=myPassword
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("H10, H13")) Is Nothing Then
    tr1 = "RT - No Reinsurance"
    tr2 = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)"
    tr3 = "RT - With Reinsurance"
  
    Select Case Range("H10").Value
      Case "Tier 1"
        If Range("H13").Value = tr1 Then Call actions(True, False, True, "", Target.Address)
        If Range("H13").Value = tr2 Then Call actions(False, False, True, "", Target.Address)
        If Range("H13").Value = tr3 Then Call actions(True, False, False, "", Target.Address)
      Case "Tier 2"
        If Range("H13").Value = tr1 Then Call actions(True, False, True, "", Target.Address)
        If Range("H13").Value = tr2 Then Call actions(False, False, False, "", Target.Address)
        If Range("H13").Value = tr3 Then Call actions(True, False, False, "", Target.Address)
      Case "Tier 3", "Tier 4":           Call actions(True, True, True, tr1, Target.Address)
    End Select
  End If
  ActiveSheet.Protect Password:=myPassword
End Sub

Sub actions(xRows As Boolean, xRng1 As Boolean, xRng2 As Boolean, xValue As String, xAdd As String)
  Rows("39:45").EntireRow.Hidden = xRows
  Range("H28:H29, K10:L23, K25:L26").Locked = xRng1
  Range("K24:L24").Locked = xRng2
  If xAdd = "$H$10" Then
    Application.EnableEvents = False
    Range("H13").Value = xValue
    Application.EnableEvents = True
  End If
End Sub
 
Upvote 0
so, the code is working fine however one criteria is not working which if hidden and unhidden rows Rows("39:45")
so for H10 has 4 choices - Type 1, Type 2, Type 3, Type 4 this decide H13 options.
for Type 1 & Type 2, there are 3 options, when "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line" are chose, rows 39:45 should be shown, else unhidden
for Type 3 & Type 4, only one options could be choose for H13, that is RT - No Reinsurance and rows 39:45 is hidden

1628699132085.png
 
Upvote 0
Sian1 said:
so, the code is working fine however one criteria is not working which if hidden and unhidden rows Rows("39:45")
Click to expand...
The code does what you ask.
Describe in which case it doesn't work. Describe step by step what you select in H10 and what you select in H13 and what should happen to the Rows("39:45").

Try this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myPassword As String, tr1 As String, tr2 As String, tr3 As String
  
  myPassword = "xxxxxxx"
  ActiveSheet.Unprotect Password:=myPassword
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("H10, H13")) Is Nothing Then
    tr1 = "RT - No Reinsurance"
    tr2 = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)"
    tr3 = "RT - With Reinsurance"
    Rows("39:45").EntireRow.Hidden = True
    Select Case Range("H10").Value
      Case "Tier 1"
        If Range("H13").Value = tr1 Then Call actions(True, False, True, "", Target.Address)
        If Range("H13").Value = tr2 Then Call actions(False, False, True, "", Target.Address)
        If Range("H13").Value = tr3 Then Call actions(True, False, False, "", Target.Address)
      Case "Tier 2"
        If Range("H13").Value = tr1 Then Call actions(True, False, True, "", Target.Address)
        If Range("H13").Value = tr2 Then Call actions(False, False, False, "", Target.Address)
        If Range("H13").Value = tr3 Then Call actions(True, False, False, "", Target.Address)
      Case "Tier 3", "Tier 4":           Call actions(True, True, True, tr1, Target.Address)
    End Select
  End If
  ActiveSheet.Protect Password:=myPassword
End Sub

Sub actions(xRows As Boolean, xRng1 As Boolean, xRng2 As Boolean, xValue As String, xAdd As String)
  Rows("39:45").EntireRow.Hidden = xRows
  Range("H28:H29, K10:L23, K25:L26").Locked = xRng1
  Range("K24:L24").Locked = xRng2
  If xAdd = "$H$10" Then
    Application.EnableEvents = False
    Range("H13").Value = xValue
    Application.EnableEvents = True
  End If
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
541
Peter_SSs
Peter_SSs
X
  • Question
Locking/unlocking a cell with a Combo Box value Mouse click problem
Replies
0
Views
496
XrayLemi
X
A
  • Solved
VBA Help!
Replies
2
Views
60
AusNewb
A
X
  • Question
Changing Thisworkbook code with code on individual sheets
Replies
3
Views
396
XrayLemi
X
S
  • Question
Help in code: locked / unlocked based on cell
Replies
2
Views
449
Sian1
S

Forum statistics

Threads
1,215,341
Messages
6,124,391
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top