Is it possible with macros?

[nivetha]

Hi All,

This is my first question and i hope this question is not a repeat (i already did a search). My requirement is like this, when a user opens a new worksheet, macro should display the list of valid keywords in the first column and it should display the another list of valid keywords based on the value selected in the first column and so on.

macro has to keep running till the user selects the keyword 'END' in the first column.

i hope my question is clear. is it possible to this in Excel macros?

- Nivetha.

 

[BrianB]

This is a way. Column A contains the main list of 10 items. Depending on which item is selected the appropriate column in the range B:K is unhidden.

The code runs each time a different cell is selected, and checks if the selected cell is in the correct range and performs the action - or not. So there is no need to have "END". If this gives a problem you may need to run it from a button.

Copy/Paste the code into the module for the worksheet. (Rightclick the tab then 'View code')

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim MainList As Range
    Dim MainListRow As Long
    Dim ShowColumn As Integer
    '--------------------------------------------------------
    Set MainList = Range("A1:A10")
    '- check list item selected
    Set isect = Intersect(Target, MainList)
    If isect Is Nothing Then Exit Sub
    '- hide/show columns
    MainListRow = Target.Row        ' row of selected cell
    ShowColumn = MainListRow + 1    ' column to show
    For c = 2 To 11
        If c = ShowColumn Then
            Columns(c).Hidden = False
        Else
            Columns(c).Hidden = True
        End If
    Next