Hide different columns depending on value in a cell

[David Kemp]

Hi everyone,

I haven’t used excel in many years, so am more than a bit rusty. I have the following code to hide Column P if cell A1 contains the value “Text 1”. I am trying to add additional arguments so if A1 equals “Text 2”, it will hide Column T or if A1 equals “Text 3” it will hide Column C and so on. Any help will be greatly appreciated!

Also, the value in A1 is a lookup return value from B1, so how could I set this code to run when cell B1 is changed?

Thank you!

 

[Peter_SSs]

Just wondering why you are using Worksheet_SelectionChange code for this? Does the value in A1 ever change simply because the selection changes?

Speaking of that, how/when does the value in A1 change? Is A a formula cell? Does A1 get changed manually by the user? Does it get changed by other vba code?

BTW, when providing your sample vba code best to copy paste it directly in your post* rather than via a picture that we cannot copy from.
* When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

 

[David Kemp]

Thanks Peter,

I copied the Worksheet_SelectionChange code because I found it on another site. I wasn't sure what it actually did! What I am actually doing is creating a spreadsheet for the administration of medications for students. In cell B1, the user selects the student's name, then A1 has a VLOOKUP that uses B1 (student name) as the lookup value, returning the medication type (Routine, As-needed, Insulin etc.) for that student from another table. So if the value in A1 is "Routine", I want it to hide certain columns, and unhide any that my have been hidden previously. I am guessing that maybe VBA can do this lookup directly, rather than using the lookup return value in A1?

So if the selected student is using:

• Routine medications, then I want to hide Columns T:BB
• Insulin, then I want to hide Columns C:RR, as well as AL:BB
• As-needed, then I want to hide Columns C:AK

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("A1").Value = "Routine" Then
        Columns("P").EntireColumn.Hidden = True
    Else
        Columns("P").EntireColumn.Hidden = False
    End If
End Sub

Any assistance would be greatly appreciated!

 

[Peter_SSs]

That's clearer, thanks. Only thing is I think you probably have a typo in the Insulin columns since columns AL:BB are already included in the range C:RR. In the code below I have assumed that you mean columns C:R, not C:RR.

Try this Worksheet_Change code (remove the Worksheet_SelectionChange code)
Leave the selection process in B1 (I assume that is using a Data Validation list) and leave the lookup formula in A1.
As it is written, the code is case-sensitive so "As-needed" needs to be exactly that and not, say, "As-Needed" etc.
You can just add more "Case" statements and the corresponding "hiding" lines if required

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    Application.ScreenUpdating = False
    Columns("C:BB").Hidden = False
    Select Case Range("A1").Value
      Case "Routine"
        Columns("T:BB").Hidden = True
      Case "Insulin"
        Columns("C:R").Hidden = True
        Columns("AL:BB").Hidden = True
      Case "As-needed"
        Columns("C:AK").Hidden = True
    End Select
    Application.ScreenUpdating = True
  End If
End Sub

 

[David Kemp]

That's clearer, thanks. Only thing is I think you probably have a typo in the Insulin columns since columns AL:BB are already included in the range C:RR. In the code below I have assumed that you mean columns C:R, not C:RR.

Try this Worksheet_Change code (remove the Worksheet_SelectionChange code)
Leave the selection process in B1 (I assume that is using a Data Validation list) and leave the lookup formula in A1.
As it is written, the code is case-sensitive so "As-needed" needs to be exactly that and not, say, "As-Needed" etc.
You can just add more "Case" statements and the corresponding "hiding" lines if required

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    Application.ScreenUpdating = False
    Columns("C:BB").Hidden = False
    Select Case Range("A1").Value
      Case "Routine"
        Columns("T:BB").Hidden = True
      Case "Insulin"
        Columns("C:R").Hidden = True
        Columns("AL:BB").Hidden = True
      Case "As-needed"
        Columns("C:AK").Hidden = True
    End Select
    Application.ScreenUpdating = True
  End If
End Sub

Thank you so much Peter! This worked a treat! You are a master!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.