Consulting

Results 1 to 12 of 12

Thread: VBA code

  1. #1
    VBAX Newbie
    Joined
    Dec 2022
    Posts
    1
    Location

    VBA code

    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
    Last edited by Aussiebear; 12-08-2022 at 12:32 PM. Reason: Added code tags to supplied code

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by georgiboy View Post
    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/show...l=1#post304622
    Last edited by p45cal; 12-08-2022 at 01:46 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by p45cal View Post
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  6. #6
    VBAX Regular
    Joined
    May 2018
    Location
    Sydney
    Posts
    57
    Location
    Quote Originally Posted by vukboban View Post
    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.
    Last edited by Aussiebear; 12-09-2022 at 03:37 AM. Reason: Added code tags to supplied code
    If you only ever do what you can , you'll only ever be what you are.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    …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/...2-d173e0ef8794 but Boban Vukovic can't be arsed to tell us.
    I don't think I'll bother trying to help vukboban again.

  9. #9
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  10. #10
    VBAX Regular
    Joined
    May 2018
    Location
    Sydney
    Posts
    57
    Location
    Quote Originally Posted by p45cal View Post
    …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/...2-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.
    If you only ever do what you can , you'll only ever be what you are.

  11. #11
    VBAX Regular
    Joined
    May 2018
    Location
    Sydney
    Posts
    57
    Location
    Quote Originally Posted by georgiboy View Post
    Don't be sad mate, you're one of the hero's of this forum.
    If you only ever do what you can , you'll only ever be what you are.

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Grade4.2 View Post
    as there are many that need your help.
    vukboban doesn't need it, he gets it elsewhere.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •