Hiding Excel columns based on cell changes

Question

understand that there are a lot of posts relating to this but I couldn't find a solution still.
There is a special condition where the Excel auto-calculation is turned off (for performance purposes for other Excel add-ons). However, I do have a button placed to recalculate the workbook.

I have two drop box which changes cell value in F38 This cell value in F38 will in turn set value "X" or "" in a specific row. What is required is that cell in the column that has "X" needs to be hidden when the cell value changes in F38.

The "X" gets populated after I click on the button to recalculate the workbook, however, the macro is not working.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Range("F38"), Range(Target.Address)) Is Nothing Then
      Dim c As Range
      For Each c In Range("H27:EU27").Cells
         If c.Value = "X" Then
            c.EntireColumn.Hidden = True    
         Else
            c.EntireColumn.Hidden = False    
         End If
      Next c
   End If
End Sub

Please advise what is being done wrong here. Currently when the cell value in F38 changes, there is no response.
This block of code is placed in the worksheet change.

Michael · Accepted Answer · 2021-12-14 04:47:47Z

The Worksheet_Change event fires when cells on the worksheet are changed by a user or by an external link. It does not fire when a value is set in a linked cell from a list object. It's always worth checking the documentation to find exactly when events fire: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change

Also, the Worksheet_Calculate event may not fire if there are not changes to calculate, even if a cell has been updated.

You could place your code in with the button to recalculate the worksheet, although this may cause the column hiding code to fire more often than you want.

The other option is to place it against the dropbox directly, to fire after the value in the dropbox has been changed by a user:

If it is a Form Control dropbox, put the code in a standalone sub and then right click on the dropbox to Assign Macro.
If it is an ActiveX Control dropbox, right click on the dropbox to View Code, then place the code in the DropBox_Change event

ZygD · Accepted Answer · 2021-12-14 08:40:07Z

0

Worksheet_SelectionChange works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.EnableEvents = False
      Dim c As Range
      For Each c In Range("H27:EU27").Cells
         If c.Value = "X" Then
            c.EntireColumn.Hidden = True
         Else
            c.EntireColumn.Hidden = False
         End If
      Next c
   Application.EnableEvents = True
End Sub

edited Dec 14, 2021 at 8:40

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Dec 14, 2021 at 6:30

Fong Wei Bin

1

Collectives™ on Stack Overflow

Hiding Excel columns based on cell changes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related