Launch a macro from a drop down selection (not a keyed in entry)

[Tanner_2004]

Hi Folks:

I would like to fire a macro (to unhide specific worksheets) when a selction is made from a data validated drop dow cell.
For example, let's assume I have a named range of comprising the names of several fruits" Apples, Bananas, Cantelope etc. One the user selects Apples from the data validated drop down list, only a separate worksheet showing apples nutrition values will be open/unhidden and the cursor would land in cell A1 (or any other cell) of the apple's nutrional values worksheet.

If the user then chooses Cantelope from the drop down list, the apple's nutritional values worksheet will be hidden or closed and the cantelope's nutritional value worksheet will be opened/unhidden with the cursor placed in cell A1.

The idea is to never have more than two worksheets open at the same time: the one with the drop down list and the one with the relavent nutritional value.

Thanks for reading!

 

[lrobbo314]

I helped someone yesterday with something similar. It uses autofilter based on your selection on sheet1. Change ranges and sheet names to suite your needs. In this example, you'd have a column header on sheet 2, Like Fruit type, and the list of fruits below it. Let me know how this works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("L" & Rows.Count).End(xlUp))
r.AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A1")
End Sub

 

[Mindpsyche]

Here is another option (I'm still new to VBA )

On a test workbook do the following:

Have 4 sheets.

Keep the first sheet name unchanged. The next three sheets name as Apples, Oranges and Mangoes.

Set up data validation of your list of names in Cell A1 of sheet1. (The list should be the 3 names Apples, Oranges & Mangoes)

In your sheet module paste this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range
    Set r = Range("A1")
    If Not Intersect(Target, r) Is Nothing Then
        Call SheetSelect
    End If
End Sub

Then in your standard module (regular macro container) paste this code:

Sub SheetSelect()
    Dim r As Range
    Dim str As String
    Dim ws As Worksheet
    Dim wscount As Long
    Dim x As Long
    wscount = ThisWorkbook.Sheets.Count
    Set r = Range("A1")
        For x = 2 To wscount
            Sheets(x).Visible = False
        Next x
        str = Range("a1").Value
        ThisWorkbook.Worksheets(str).Visible = True
End Sub

Keep in mind one thing regarding the event change. You have to also click on the cell to trigger the event change when choosing the items from the list. Just choosing it from the list will not trigger it. Click on another cell and then re select A1 to trigger it (If it has not triggered already).

 

[Vidar]

My take on this:
The sheet you always will have visible must be the first.
Name the other sheets the fruit names.
My Validation list is in cell A1. Edit the code if it's in another cell on your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh As Worksheet
    If Target.Address = "$A$1" Then
        For Each sh In Application.Worksheets
            If sh.Index > 1 Then
                If sh.Name = Target.Value Then
                    sh.Visible = True
                Else
                    sh.Visible = False
                End If
            End If
        Next
    End If
End Sub

Vidar

 

[Tanner_2004]

Thank you. I will give these a try!

 

[Tanner_2004]

I wonder if I coudl circumvent clicking on the cell by embedding the drop down box on a form and then click ok?

Here is another option (I'm still new to VBA )

On a test workbook do the following:

Have 4 sheets.

Keep the first sheet name unchanged. The next three sheets name as Apples, Oranges and Mangoes.

Set up data validation of your list of names in Cell A1 of sheet1. (The list should be the 3 names Apples, Oranges & Mangoes)

In your sheet module paste this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range
    Set r = Range("A1")
    If Not Intersect(Target, r) Is Nothing Then
        Call SheetSelect
    End If
End Sub

Then in your standard module (regular macro container) paste this code:

Sub SheetSelect()
    Dim r As Range
    Dim str As String
    Dim ws As Worksheet
    Dim wscount As Long
    Dim x As Long
    wscount = ThisWorkbook.Sheets.Count
    Set r = Range("A1")
        For x = 2 To wscount
            Sheets(x).Visible = False
        Next x
        str = Range("a1").Value
        ThisWorkbook.Worksheets(str).Visible = True
End Sub

Keep in mind one thing regarding the event change. You have to also click on the cell to trigger the event change when choosing the items from the list. Just choosing it from the list will not trigger it. Click on another cell and then re select A1 to trigger it (If it has not triggered already).

 

[Tanner_2004]

Thank you!

 

[Mindpsyche]

Your welcome thanks for the feedback.