Run a macro depending on cell value

[Esradekan]

I want to run a macro depending on the cell value, selected from a
drop down box.

Lets say that data to select from is numbers 1 - 20
Lets say I wish to take the person from Sheets 1 - 20 accordingly

How can I do that

TIA

Esra

 

[ilia]

You can monitor the worksheet change event, and call up the correct
sheet accordingly. For instance, let's say the data you want to pull
up is in A1 of Sheets 1-20, the values on your main sheet is in A1,
and you want to pull the data in B1. Insert this code into Main
sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
If Not Intersect(Target, Me.Range("A1")) Then
d = CInt(Me.Range("A1").Value)
If (d >= 1 And d <= 20) Then
Me.Range("B1").Value = _
ThisWorkbook.Worksheets("Sheet" & d).Range("A1").Value
End If
End If
End Sub

Hopefully this is what you meant.

 

[ilia]

Oops! Here is what I actually meant:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
d = CInt(Me.Range("A1").Value)
If (d >= 1 And d <= 20) Then
Me.Range("B1").Value = _
ThisWorkbook.Worksheets("Sheet" & d).Range("A1").Value
End If
End If
End Sub