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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,707
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
53,707
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
53,707
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
53,707
Office Version
365
Platform
Windows
You are welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,682
Messages
5,488,239
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top