TTom
Well-known Member
- Joined
- Jan 19, 2005
- Messages
- 518
I need some help adjusting vba code. This is an area I'm still learning about so this might not be the best approach to problem?
The current test code (shown) works when I change boolean values myself on active sheet.
<u>My question follows after the code</u>...
For code below, if:
Range("C40")=true hide rng_A, if false unhide rng_A
Range("C41")=true hide rng_B, if false unhide rng_B
Range("C42")=true hide rng_C, if false unhide rng_C
Code is in VBA's Sheet1, all named ranges are on Sheet1
<code>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 Then 'Targets Column C
ThisRow = Target.Row
If Target.Value = True Then
If ThisRow = 40 Then 'Targets Range("C40") if True
Range("rng_A").Select
Selection.EntireRow.Hidden = True 'Hides rng_A when Range("C40") is True
End If
If ThisRow = 41 Then
Range("rng_B").Select
Selection.EntireRow.Hidden = True
End If
If ThisRow = 42 Then
Range("rng_C").Select
Selection.EntireRow.Hidden = True
End If
Else:
If ThisRow = 40 Then
Range("rng_A").Select
Selection.EntireRow.Hidden = False
End If
If ThisRow = 41 Then
Range("rng_B").Select
Selection.EntireRow.Hidden = False
End If
If ThisRow = 42 Then
Range("rng_C").Select
Selection.EntireRow.Hidden = False
End If
End If
End If
Range("A1").Select
End Sub
</code>
What I need to do is have true/false be a formula result, not a direct entry.
Example: Sheet1!C40 is =IF(Sheet3!A1=1,TRUE,FALSE)
So, when I change Sheet3!A1 value to 1, rng_A on Sheet1 will become hidden next time I view the sheet.
If I use Worksheet_Activate, or Worksheet_Calculate, I get an error if I use (ByVal Target As Excel.Range).
TTom
The current test code (shown) works when I change boolean values myself on active sheet.
<u>My question follows after the code</u>...
For code below, if:
Range("C40")=true hide rng_A, if false unhide rng_A
Range("C41")=true hide rng_B, if false unhide rng_B
Range("C42")=true hide rng_C, if false unhide rng_C
Code is in VBA's Sheet1, all named ranges are on Sheet1
<code>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 Then 'Targets Column C
ThisRow = Target.Row
If Target.Value = True Then
If ThisRow = 40 Then 'Targets Range("C40") if True
Range("rng_A").Select
Selection.EntireRow.Hidden = True 'Hides rng_A when Range("C40") is True
End If
If ThisRow = 41 Then
Range("rng_B").Select
Selection.EntireRow.Hidden = True
End If
If ThisRow = 42 Then
Range("rng_C").Select
Selection.EntireRow.Hidden = True
End If
Else:
If ThisRow = 40 Then
Range("rng_A").Select
Selection.EntireRow.Hidden = False
End If
If ThisRow = 41 Then
Range("rng_B").Select
Selection.EntireRow.Hidden = False
End If
If ThisRow = 42 Then
Range("rng_C").Select
Selection.EntireRow.Hidden = False
End If
End If
End If
Range("A1").Select
End Sub
</code>
What I need to do is have true/false be a formula result, not a direct entry.
Example: Sheet1!C40 is =IF(Sheet3!A1=1,TRUE,FALSE)
So, when I change Sheet3!A1 value to 1, rng_A on Sheet1 will become hidden next time I view the sheet.
If I use Worksheet_Activate, or Worksheet_Calculate, I get an error if I use (ByVal Target As Excel.Range).
TTom