Create a macro that would hide and show columns based on selection from a dropdown

I am totally new to the world of macros but have done some VBScript on QTP before. I am looking to create a macro which would hide certain columns depending on the users selection from a drop down. I am unsure of the syntax and how to identify the columns I wish to hide and how to identify the cell with the drop-down.

Here's roughly how I would see it looking -

Sub HideColumns()
    If cell(开发者_如何转开发ViewType).Value = "Option 1" Then
       Cells(Column_1, Column_2).EntireColumn.Hidden = True
    ElseIf cell(ViewType).Value = "Option 2" Then
       Cells(Column_2, Column_3).EntireColumn.Hidden = True
    ElseIf cell(ViewType).Value = "Option 3" Then
       Cells(Column_3, Column_4).EntireColumn.Hidden = True
    End If
End Sub 

I have named the cell with the drop-down ViewType in Excel - would the VBA recognize the object that way, or would I need to declare it?

How do I identify the columns I wish to hide?

The names you define in Excel are available in Excel as normal variables, so this should not be of any issue.

In your case, I would however suggest using a Switch Case statement. This would look as follows:

Select Case ActiveWorkbook.Names("ViewType").RefersToRange
         Case "Option 1" 
              ' Hide Column X
         Case "Option 2" 
              ' Hide Column Y
End Select

Also keep in mind that for the macro to be called once you change a cell, you would need to put this code into

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

This Sub has to be placed in the code part of the Sheet itself and will be executed every time a cell is changed in the Sheet.

Let me know if this is enough for you to go on or if you require more help.

I like to use Custom Views when hiding and unhiding columns. Custom views is on the View tab (>=2007) and under the View menu (<=2003). Here's an example:

1. Hide columns 1 and 2
2. View - Custom Views - Add - name it "Option1"
3. Unhide, then hide columns 2 and 3
4. View - Custom Views - Add - name it "Option2"
5. Unhide, then hide columns 3 and 4
6. View - Custom Views - Add - name it "Option3"
7. Unhide
8. Create Data Validation in a cell (that doesn't get hidden) and make is a List with "Option1, Option2, Option3"
9. Name that cell ViewType

10. Right click on the sheet tab and choose View Code and put the below code in that code pane

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        'Only work on the cell named ViewType
        'The Me keyword refers to the sheet whose code module you're in
        If Target.Address = Me.Range("ViewType").Address Then
            'Show the custom view that corresponds to the value selected
            'in the dropdown
            ActiveWorkbook.CustomViews(Target.Value).Show
        End If
    
    End Sub
    

When the user selection Option1 from the drop down, the Option1 view is shown that hides columns 1 and 2. It's a nice way to manage hiding and unhiding because you can just adjust the Custom View if you ever want to change it, rather than editing the code.