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]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
You are welcome.
Glad I was able to help!
:)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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