Hiding Excel columns based on cell changes-CodePudding

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.

CodePudding user response：

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://docs.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

CodePudding user response：

thanks for your prompt reply.

Previously I have this code (I have removed most of the EntireColumn.hidden statements as they are too long, as well as most of the case statements). Since I added more case statements and I have too many columns this manual way is not feasible and running it causes error saying the procedure is too large. Therefore I am trying this solution of using "X" marking the columns that need to be hide.

Any idea why this code works (if its not too large) but the one I posted in my original question doesn't?

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("F38"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "XXX":
        Columns("H").EntireColumn.Hidden = False
        Columns("I").EntireColumn.Hidden = False
        Columns("J").EntireColumn.Hidden = True
    Case Is = "YYY":
        Columns("H").EntireColumn.Hidden = False
        Columns("I").EntireColumn.Hidden = True
        Columns("J").EntireColumn.Hidden = False
    End Select
End If
End Sub

I'll try next:

Changing to worksheet.calculate event
Changing the track cell change from F38 to the range of cells which is calculated (the cells that are populated with X) by cell F38

CodePudding user response：

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