VBA explanation please.
Results 1 to 10 of 10

Thread: VBA explanation please.

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA explanation please.

    Can someone explain what this code is doing? thanks in advance.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice 20160725
        Dim xCell As Range
        If Target.Address <> Range("C54").Address Then Exit Sub
        Application.ScreenUpdating = False
        For Each xCell In Range("D7:S7")
            xCell.EntireColumn.Hidden = (xCell.Value > Target.Value)
        Next
        Application.ScreenUpdating = True
    End Sub

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA explanation please.

    If cell C54 is manually updated, the code will run and compare each of the values in the range D7:S7 to the new value in cell C54.
    For each of those cells, if their value is greater than the value in cell C54, it will hide that entire column.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA explanation please.

    Thanks for the reply Joe4!!!

    I need to apply this code to the rest of the sheets in the workbook as well. However, the range to be evaluated to C54 of sheet1 on the rest of the sheets is E2:T2. How can I apply this to the remaining sheets? will it have to be entered in each sheet, or is there a common way to apply it?

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA explanation please.

    The Worksheet_Change code is specific to the sheet module that the code is located in. So it would need to be placed in each sheet module.
    Though, I think there might be a way to do it across all sheets using Workbook_SheetChange.
    See here: https://www.mrexcel.com/forum/excel-...nge-event.html
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA explanation please.

    I'll look at that, thank you!!

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA explanation please.

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Oct 2010
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA explanation please.

    ok, Being that I am not the sharpest knife in the drawer, would it be easier if I were to send the sheet to you? If you have time to look at it?
    Last edited by DButler; Aug 23rd, 2019 at 12:06 PM.

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA explanation please.

    OK, in re-reading it, I think I originally misinterpretted your follow-up request. It sounds like you are only concerned about changes to C54 on Sheet1 (not every sheet), but want it to affect every sheet.
    If that is the case, then you can continue to use the Worksheet_Change event procedure where it is, just use this variation, which loops through all sheets:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim orgVal As Double
        Dim xCell As Range
        Dim ws As Worksheet
    
    '   If cell C54 not updated on first sheet, exit sub
        If Target.Address <> Range("C54").Address Then Exit Sub
        
    '   Capture value of C54
        orgVal = Range("C54").Value
        
        Application.ScreenUpdating = False
    '   Loop through all sheets
        For Each ws In Worksheets
            With ws
                For Each xCell In .Range("E2:T2")
                    xCell.EntireColumn.Hidden = (xCell.Value > orgVal)
                Next xCell
            End With
        Next ws
        Application.ScreenUpdating = True
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Oct 2010
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA explanation please.

    You are the MAN!!! works like a charm. Thanks so much!!!

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA explanation please.

    You are welcome.
    Glad I was able to help!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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