In Excell - VB code to trigger

[desi5pure]

In Excell- How can I trigger a VB code (to go to a particular tab) when someone picks a value from a dropdown in a cell? I have different tab for each value in the dropdown and would like to go to that particular tab when the value is picked.

 

[SkipVought]

Hi,

What kind of control? Form, Control toolbox, Data Validation?

It seems that it may be a Data Validation - LIST control.

Right-click the Sheet Tab and select View Code

Paste this code...

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets(Target.Value).Activate
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ddcroit]

desi5pure,

I would create a combobox.

In the ListFillRange Property of the Combobox select the range where you sheet's names are.

in design mode right click the Combobox and select view code.

You can paste this code:

Private Sub ComboBox1_Change()
On Error GoTo NoMatch
Sheets(ComboBox1.Value).Select
Exit Sub
NoMatch:
MsgBox "No Sheet name as " & ComboBox1
End Sub

 

[SkipVought]

ddcroit,

The OP has already stated, "when someone picks a value from a dropdown in a cell."

By inferrence, it seems like this is a Data Validation - LIST control, and not a ComboBox.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

Let's say you are using datavalidation.
The user select pulldown in cell. You have named that cell MySelectCell (named range).

The cell has data validation turned on using a list.
The list allows values such as
dataforsheet2
dataforsheet3
dataforsheet4


Use the following code in the worksheet change event to go to the associated tab:

Private Sub Worksheet_Change(ByVal Target As Range)

' Assume the selection cell is a named range "MySelectCell
' Test for changed cell in the selection cell
If Target.Address = Sheets("Sheet1").Range("MySelectCell").Address Then
Select Case Target(1, 1)
Case "dataforsheet2": Sheets("Sheet2").Activate
Case "dataforsheet3": Sheets("Sheet3").Activate
Case "dataforsheet4": Sheets("Sheet4").Activate
End Select
End If

End Sub

 

[SkipVought]

Yes, I seemed to have missed an important part: the location of the Data Validation Cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Assume the selection cell is a named range "MySelectCell"
   if not intersect(target,[MySelectCell]) is nothing then
      Sheets(Target.Value).Activate
   end if
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]