How do I create a macro that would hide and show columns based on selection from a dropdown?

Question

Hello

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 dropdown.

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 dropdown "ViewType" in excel - would th vb recognise the object that way, or would I need to declare it?

How do Identify the columns I wish to hide?

Thanks for your help!

Thom

Answer 1

Hi ThomB,

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.

Cheers,