Select a worksheet based on excel drop down

[levans_1986]

Hello

I am creating a questionnaire which comprises of three excel worksheets. On the first worksheet one of the questions has a yes/no answer in a dropdown. I want to automatically select a worksheet depending on the answer to the question

For example:
If someone selects yes they are automatically taken to worksheet 2(PartA). If they select no they are automatcially taken to worksheet 3(PartB).

I have recorded two macros, Mac_PartA which takes you to sheet 2 and Mac_PartB which takes you to sheet 3. I've tried to use some vba code but I'm not overly familar with it and I am getting a compile error. the code I have is saved in the workbook rather than the sheet

the code is
Private Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Range("P2")) Is Nothing Then Select Case Range("P2")

Select Case Range("P2")

Case "Yes": Mac_PartA
Case "No": Mac_PartB
End Select
End If
End Sub

Can anyone help?

Many thanks in advance

Laura

 

[Joe4]

Try putting your VBA code in a Worksheet_Change event procedure (which runs upon a change to a cell, like P2) instead of a Worksheet_SheetChange event procedure.

 

[MrKowz]

You had two Select Case statements, one in-line with the If-Then statement, one on the next line.

Try:

Private Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Range("P2")) Is Nothing Then
    Select Case Range("P2")
        Case "Yes": Mac_PartA
        Case "No": Mac_PartB
    End Select
End If
End Sub

 

[Molden]

Edited as I misread

 

[levans_1986]

Thanks MrKowz

I have tried this but I am still getting a compile error. Any thoughts?

 

[levans_1986]

Thanks Joe4. Sorry to be dumb, new to this, how do I put it into a worksheet change event proceedure?

thanks

 

[Joe4]

In the VB Editor go to the VB module of the sheet that this drop-down box appears in.
Then, above the VB Editor, you will see two drop down boxes.
From the one of the left, select "Worksheet"
From the one on the right, select "Change"

This will give you the shell of the Worksheet_Change event procedure, which looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

Just take the body of the VB code from what you already have (everything but the first and last lines), and paste it in between the two lines shown above.

 

[levans_1986]

Thank you so much that works! However only other issue is I want this macro to trigger if any cell in column p after p1 changes. at the moment it only triggers when p2 changes. how do I get the whole column to change?

Thanks again your help is much appreciated

 

[Joe4]

I would make a few minor modifications like this:

Private Sub Worksheet_Change(ByVal Target As Range)


'   Exit if more than one cell updated at a time
    If Target.Count > 0 Then Exit Sub
    
'   Only run if a cell in column P after row 1 is updated
    If Target.Column = 16 And Target.Row > 1 Then
        Select Case Target.Value
            Case "Yes": Mac_PartA
            Case "No": Mac_PartB
        End Select
    End If


End Sub

 

[levans_1986]

Joe4 you star thank you

Thanks everyone for your help