Method to display list of options when cell selected

[davidb50]

Hi

I'm looking for a method which presents the user with a list of options when they either hover over a cell or when they click on the cell. When they select an option this will then trigger some code to activate the appropriate existing worksheet in the same workbook. It's basically a method that allows users to drill through data held within the workbook.

Have tried a few approaches: (a) inserting hyperlinks into comment box doesn't seem to work as can only have one hyperlink per comment box, (b) same issue with using shapes, (c) tooltips just seem to display information and don't appear to allow a method to select from these, (d) I don't want to customise the menu that appears on right mouse click as this will conflict with another add-in that is running, (e) pivot table drill down will not allow me to provide the user with options.

Would be very grateful for any pointers that either show me how a-c above could be made to work or suggest a better approach.

Thanks
David

 

[Sektor]

You need SelectionChange event handler in worksheet's module. Here's simple example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "A1"
            '// Some code
        Case "A2"
            '// Another code
    End Select
End Sub

 

[davidb50]

Hi

Thanks for the reply. Sure, I can use the event to trigger options that are dependent on which Target cell is clicked. However, what I'm looking for is a method to display a list of options to the user based on a target cell being clicked - I want a box to hover next to the cell which lists options to select from. Apologies if the question wasn't sufficiently clear on that.

Thanks
David

You need SelectionChange event handler in worksheet's module. Here's simple example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "A1"
            '// Some code
        Case "A2"
            '// Another code
    End Select
End Sub

 

[Sektor]

Upon selecting cell, you could use Data Validation to add options (say, to the cell next to active).

 

[davidb50]

Thanks for the good suggestion - I can definitely see how this could be made to work when coupled with Worksheet_Change events

Upon selecting cell, you could use Data Validation to add options (say, to the cell next to active).

In an ideal world (which might not exist) I'd still like to find a way of presenting the options from a single cell. Have just stumbled across Smart Tags - perhaps might be something in this?