VBA Code for Hiding Column on Sheet Based on Cell Value

[giblahblah]

I am BRAND NEW to VBA coding and have tried to figure out how to write what should be a simple code with zero success. I'd like to attach the workbook, but I don't have privileges in this forum.

Summary of goal:

I have one tab called "Instructions TM1" and another tab called "Ops and ADS Summary"

I need code for:

If on the "Instructions TM1" tab cell B37 says “Day_16” then hide columns H, V, AE, and AS on the "Ops and ADS Summary" tab.

If on the "Instructions TM1" tab cell B37 says “Day_21” then unhide columns H, V, AE, and AS on the "Ops and ADS Summary" tab.

I've tried multiple iterations of codes I've found on forums with no luck. Can anyone write out code for this scenario that I can test? Also, any explanations on how Dim functions work is appreciated. I believe not defining variables may be my problem?

Thanks in advance.

-Jeff

 

[Rick Rothstein]

If on the "Instructions TM1" tab cell B37 says “Day_16” then hide columns H, V, AE, and AS on the "Ops and ADS Summary" tab.

If on the "Instructions TM1" tab cell B37 says “Day_21” then unhide columns H, V, AE, and AS on the "Ops and ADS Summary" tab.

What should happen if that cell says anything else (for example, Day_17, Day_19, etc. or even any other text or numbers)?

 

[giblahblah]

Cell B37 is a drop down validation cell, so Day_16 and Day_21 are the only options available for selection.

 

[Fluff]

Hi & welcome to MrExcel.
As you didn't say what should happen if B37 equals anything other than those two values, this will hide the columns if B37 is Day_16 & unhide them for any other value

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B37" Then
      With Sheets("Ops and ADS Summary")
         .Range("H:H,V:V,AE:AE,AS:AS").EntireColumn.Hidden = Target.Value = "Day_16"
      End With
   End If
End Sub

This code needs to go in the "Instructions TM1" code module

 

[giblahblah]

This code worked great! Thank you for the quick response. Unfortunately, I don't think I'd ever get to this code on my own. Is there any resource you could point me to that would educate me on the different components within this code?

 

[Fluff]

Private Sub Worksheet_Change(ByVal Target As Range)
1        If Target.CountLarge > 1 Then Exit Sub
2        If Target.Address(0, 0) = "B37" Then
3           With Sheets("Ops and ADS Summary")
4              .Range("H:H,V:V,AE:AE,AS:AS").EntireColumn.Hidden = Target.Value = "Day_16"
5           End With
6        End If
End Sub

Target is the cell or cells that have been change
1) Checks if more than 1 cell has been changed, if so code exits
2) Checks the address of the cell is B37
4) this Target.Value = "Day_16" evaluates to either TRUE or FALSE depending on whether the target equals Day_16
This result is then passed to the first part of the line & hides/unhides the columns

HTH