How can I use VBA to hide particular columns in another sheet using a dropdown in the first sheet?

Your code looks fine to me and it should hide the columns T:V on Calculation Sheet no matter which sheet is currently active.
Can you upload a sample workbook?

Also you can omit the Else condition here because in both the cases you are setting dws to Calculation sheet. So use OR in IF to check both the condition at once.

The code works fine for me. Has sheet 1 (sWS) got 2-5 years in I17 or 2-6years, make sure that spaces match within the text

@Roy

If I17 doesn't contain any of the two set strings in it, the code will not hide any column on any sheet as the hidden statement is inside the IF block and it will only be executed once one of the condition is evaluated as True. :)

IK was asking the author if the text actually exists in the worksheet and if it is exactly as required in the code. An extra space can affect the code.

This is what asker said in the description...

The closest I've got is this code but that only hides them on the sheet I'm on:

I've checked the code and the text is exactly as it is in I17.
I've also tried removing the Else and that doesn't work

Does the code need to be in a module or in the object, within the sheet?  At the moment it's in the 'Sheet1' object.

It would be helpful, if you upload your sample workbook with code

I ran it from a standard module and it worked fine

Yes the code should be placed on a Standard Module like Module1 etc.

This is tidier

Option Explicit
Private Sub Test2()
    Dim sws As Worksheet, dws As Worksheet
    Set sws = Sheets("Sheet1")
    Set dws = Sheets("Calculation")

    Select Case sws.Range("I17").Value
    Case "2-5 years"
        dws.Columns("Q:V").Hidden = True
    Case "2-6 years"
        dws.Columns("T:V").Hidden = True
    End Select

End Sub

Select allOpen in new window

Place it in a standard module
EE-example.xlsm

Here's a sample worksheet. Thank you.
Booktest.xlsm

You don't have a sheet called Calculation, see my example

The last one Roy works when I click the play button so I'm guessing there's some sort of option I've got that means this isn't running automatically on changing I17?

Yes, in my sample I changed 'Calculation' to 'Sheet2'

Remember that you also have to remove the existing code from Module1 before pasting the suggested code on Module1.

Thank you.  It runs when I click on the RunSub button - is there an easy way of making this automatic on changing the selection in I17, whether in the code or general settings?

Thank you very much! Works a treat.  I reeeeeallly appreciate your help!

You're welcome James! Glad it worked.
Thanks for the feedback.

I've got another challenge!
I'm trying to hide shapes depending on I15 cell selection.  This is sort of an amalgamation between the above code and something I found online but it's not working, any ideas?

Private Sub HideObj(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "I15" Then
    Dim myshape As Shape
    For Each myshape In ActiveSheet.Shapes
    If myshape.Type = 1 Then myshape.Visible = True
    Application.EnableEvents = False
    If Range("I15").Value = "Yes" Then
    Dim myshape As Shape
    For Each myshape In ActiveSheet.Shapes
    If myshape.Type = 1 Then myshape.Visible = True
    ElseIf Range("I15").Value = "No" Then
    Dim myshape As Shape
    For Each myshape In ActiveSheet.Shapes
    If myshape.Type = 1 Then myshape.Visible = False
    Next myshape
           End If
    Application.EnableEvents = True
End If
End Sub

Hi James,

Please open a New Question and post the line of new question here for reference.

Thanks.

https://www.experts-exchange.com/questions/29004896/How-to-hide-shapes-in-VBA-based-on-a-particular-cell-with-Data-validation.html