VBA explanation please.

DButler

Board Regular
Joined
Oct 30, 2010
Messages
158
Can someone explain what this code is doing? thanks in advance.

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]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[/FONT]
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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.
 

DButler

Board Regular
Joined
Oct 30, 2010
Messages
158
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?
 

DButler

Board Regular
Joined
Oct 30, 2010
Messages
158
I'll look at that, thank you!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
You are welcome.
 

DButler

Board Regular
Joined
Oct 30, 2010
Messages
158
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
You are welcome.
Glad I was able to help!
:)
 

Forum statistics

Threads
1,089,201
Messages
5,406,801
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top