Worksheet_Change? Need some help with code tweak...

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). :confused:
TTom
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Tom

You can't use the change event for formula results.

And you can't use Target in Activate/Calculate because they don't actually have any paramaters/arguments so it doesn't really exist.

By the way I'm a little confused by the structure of the code, you seem to be checking for the same row more than once.

Oh, and you probably don't need Select/Selection.
Code:
Range("rng_B").EntireRow.Hidden = True
 
Upvote 0
Hello Tom,


1) the problem with passing the argument ByVal is that the Range is an object and not a int, string etc.

2) Your formula should work like you've written it and it should make the calculation automatically. You should'nt need to use the Changed event.
 
Upvote 0
Norie:
I shortened the code removing select as you recommended.
The section 'If Target.Value = True Then' hides ranges
the Else: unhides hidden ranges if value change back from True to False.

Emanresux:
As said, if I change True/False as 'values' the code works.
However, if I change to a formula result for Boolean, it doesn't work. See my example formula.
i.e: I change a value on SHEET3, should hide/unhide rows on SHEET1 when next viewed/activated.

Any other suggestions? Thanks for replies... :wink:
 
Upvote 0
Tom

I don't think you quite understand.:)

The 2 other events you mention don't get anything, ie Target, passed to them.
 
Upvote 0
Yup! I'm confused. I'm a newbie to this area of code.
So, can this not be done, or I just don't understand what I need to do?
:rolleyes: TTom
 
Upvote 0
Reading my text book... scratching my head.
Is this a task better handled by an Application-level event in a class module?
Maybe I need some better reading to educate myself?
Suggestions? TT
 
Upvote 0
TTom,

This would check C40:C42 and hide the rows if true, unhide if false each time there is a calculation done on the sheet.

Code:
Private Sub Worksheet_Calculate()

For Each Ce In Range("C40:C42")
    If Ce.Value = True Then
        Ce.EntireRow.Hidden = True
    Else
        Ce.EntireRow.Hidden = False
    End If
Next

End Sub
 
Upvote 0
Actually, I am not sure what I was thinking before, but this would do the same thing with less

Code:
Private Sub Worksheet_Calculate()
Dim Ce as Range

For Each Ce In Range("C40:C42")
    Ce.EntireRow.Hidden = Ce.Value
Next

End Sub
 
Upvote 0
Brian,
Thanks. I had to modify slightly as I want the to trigger hide or unhide of named range associated to each boolean ce.value; however, the code:
Private Sub Worksheet_Calculate()
Dim Ce as Range
This is what I needed to get me going. :cool:
I expect this is what the others meant, but I understand more now.
Thanks to everyone who replied. :biggrin:
TTom
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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