Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Cell activity determing action

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am looking to write a macro so that when a user inputs items in a cell and 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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Troy

    Here is another way:


    Code:
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •