VBA code

[vukboban]

This code works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim RNG As Range
Set RNG = Range("A28:A31")
Set WS = Sheets("Sheet2")
WS.Range("A28:A31").EntireRow.Hidden = True
End Sub
but I need to replace it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim RNG As Range
Set RNG = Range("A28:A31")
Set WS = Sheets("Sheet2")
WS.RNG.EntireRow.Hidden = True
End Sub
And this doesnt work.
Any help ?
Thank you

[georgiboy]

You would not need to set the WS as in a Worksheet_Change event the WS is the active sheet, you don't really need to define the range either as you can apply your command to the range directly. It will hide those rows every time a change is made anywhere on the sheet, not sure if that is the goal but the code to do what you did originally would be like:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Range("A28:A31").EntireRow.Hidden = True
End Sub

[p45cal]

as in a Worksheet_Change event the WS is the active sheet
While very likely true in this case, it's not a given. VBA code can change cells on an inactive sheet, and that inactive sheet's Worksheet_Change event would fire even though it's not the active sheet.
Also the Me prefix isn't required (no harm in it being there, mind you) as any unqualified reference in a sheet's code-module refers to that sheet, active or not.

see http://www.vbaexpress.com/forum/showthread.php?48874-Worksheet_change-event-executed-on-a-wrong-sheet&p=304622&viewfull=1#post304622

[Paul_Hossler]

Not tested, but try this mod




Private Sub Worksheet_Change(ByVal Target As Range)

Dim RNG As Range

Set RNG = Sheets("Sheet2").Range("A28:A31")

RNG.EntireRow.Hidden = True

End Sub

[georgiboy]

While very likely true in this case, it's not a given. VBA code can change cells on an inactive sheet, and that inactive sheet's Worksheet_Change event would fire even though it's not the active sheet.
Also the Me prefix isn't required (no harm in it being there, mind you) as any unqualified reference in a sheet's code-module refers to that sheet, active or not.

Thanks p45cal, this is something i am aware of but tend to assume that these pieces of code are being run on the active sheet as most of the time in the forums i frequent this is the case. I am not the best at explaining things sometimes as being purely self-taught i don't always go about things in the best possible way, this can be more evident in my explanations, though i think it is important to explain things the best i can as i picked up a lot of what i know from others on this forum that do the same. There are a few on the forum that send code with no explanation which i think does not always benefit the community and therefore hinders a learning opportunity.

As for the Me reference, i do this out of habit as it seems to carry over in my mind from when i write code for userforms - it is almost as if sheet level code i treat the sheet to be a userform in the way that i reference it, i don't always do it - it must depend on how much UF code i have created in that week.

Always good to be clear though and i am sure the OP will benefit from it.

Cheers

George

[Grade4.2]

This code works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim RNG As Range
Set RNG = Range("A28:A31")
Set WS = Sheets("Sheet2")
WS.Range("A28:A31").EntireRow.Hidden = True
End Sub
but I need to replace it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim RNG As Range
Set RNG = Range("A28:A31")
Set WS = Sheets("Sheet2")
WS.RNG.EntireRow.Hidden = True
End Sub
And this doesnt work.
Any help ?
Thank you

Here is one way you could fix the code:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim RNG As Range
' Check if the target range is within the specified range
If Intersect(Target, Range("A28:A31")) Is Nothing Then
Exit Sub
End If
' Set the worksheet and range variables
Set WS = Sheets("Sheet2")
Set RNG = WS.Range("A28:A31")
' Hide the entire row of the changed range
RNG.EntireRow.Hidden = True
End Sub


In this code, the RNG variable is set to the range A28:A31 on the current worksheet. However, in the line WS.RNG.EntireRow.Hidden = True, the RNG variable is being used as if it is a property of the WS worksheet object. This won't work because RNG is a local variable within the Worksheet_Change subroutine, and it is not a property of the WS worksheet object.


To fix this, you could simply use the Range property of the WS worksheet object to reference the A28:A31 range, like this: WS.Range("A28:A31").EntireRow.Hidden = True. This will hide the entire row of the A28:A31 range on the Sheet2 worksheet.


Alternatively, you could use the RNG variable directly, like this: RNG.EntireRow.Hidden = True. This will also hide the entire row of the A28:A31 range on the Sheet2 worksheet, because the RNG variable was previously set to that range using the Set keyword.

[p45cal]

Looking at the differences between the two macros in msg#1 the only difference is between
WS.Range("A28:A31").EntireRow.Hidden = True
and
WS.RNG.EntireRow.Hidden = True

The first doesn't use RNG
If the second complains at that line then I strongly suspect that RNG is NOT on "Sheet2" so WS.RNG… is the problem. It looks like the code is intended to hide rows on a sheet which is not the same sheet that the code is in?
So I suspect that a version of georgiboy's one liner is almost guaranteed to work:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Range("A28:A31").EntireRow.Hidden = True
End Sub

[p45cal]

…and here we are scrabbling around trying to find an answer for vukboban while he has a very good answer yesterday at https://answers.microsoft.com/en-us/msoffice/forum/all/using-range-variable-in-vba/dddcb01b-2c02-430c-9102-d173e0ef8794 but Boban Vukovic can't be arsed to tell us.
I don't think I'll bother trying to help vukboban again.

[georgiboy]

:mkay

[Grade4.2]

…and here we are scrabbling around trying to find an answer for vukboban while he has a very good answer yesterday at https://answers.microsoft.com/en-us/msoffice/forum/all/using-range-variable-in-vba/dddcb01b-2c02-430c-9102-d173e0ef8794 but Boban Vukovic can't be arsed to tell us.
I don't think I'll bother trying to help vukboban again.

You're doing an amazing job mate, keep it up as there are many that need your help.

[Grade4.2]

:mkay

Don't be sad mate, you're one of the hero's of this forum. :yes:clap::hi::cool::bow:

[p45cal]

as there are many that need your help.vukboban doesn't need it, he gets it elsewhere.