Cell activity determing action

[Troy]

I am looking to write a macro so that when a user inputs items in a cell and <enter> the user will be taken to a different sheet. For example: Use enters 2 in B2 then they will be taken to Sheet2, If 3 in B2 then Sheet3, anything else entered in B2 the user would be taken to Sheet4.

 

[lenze]

A change Event in the sheet code should do what you want. Something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If Target.Value = 2 Then Sheets("Sheet2").Activate
If Target.Value = 3 Then Sheets("Sheet3").Activate
If Target.Value >= 4 Then Sheets("Sheet4").Activate

End If

End Sub

Modify if needed

 

[Dave Hawley]

Hi Troy

Here is another way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Integer
Dim strName As String

If Target.Column <> 2 Then End

   lRow = Target.Row
     If lRow > 3 Then
       Sheets("Sheet4").Select
     Else
       strName = Choose(lRow, "Sheet1", "Sheet2")
       Sheets(strName).Select
    End If
    
End Sub