Can't advance to another sheet

Question

The below code is written into the code of a Sheet (called "Start page") rather than connected to a button.

For reference, cell C17 asks the user of the sheet if they're ready to start, and they have a data validation in D17 linking to M2 (Yes) and M3 (No). The idea is that when the user selects Yes, the code kicks in and auto-advances to the next sheet ("Selection page"). If they select No, a pop-up box will display advising the workbook will close (I've not written this code yet).

The problem is, whilst I can't see what's wrong with this code, whenever the user selects anything from the data validation, nothing happens.

I'd be really grateful if someone could review the code to see what I'm missing. I've tried Range("D17") and Range("D17").Value but still nothing. Thanks

EDIT - latest code listed below

Private Sub Worksheet_Change()
Dim output As Integer

Application.ScreenUpdating = False

Dim output As Integer

    If Sheets("Start page").Range("D17") Is Nothing Then GoTo ExitHandler

    If Sheets("Start page").Range("D17") = Sheets("Start page").Range("M2") Then
        Sheets("Selection page").Activate
    Else
        output = MsgBox("This sheet will now close.", vbCritical, "Closing")
        Activebook.Close

ExitHandler:
    Application.ScreenUpdating = True

End Sub

Fully qualify your range objects and then you wont have to use `Activate` at all. — braX, Apr 06 '20 at 09:53
Thanks @braX. I chose ```Activate``` rather than ```Select``` because I've read advice to not use ```Select```. Nevertheless, I have amended the code to ```If Range("D17") = Range("M2") Then Sheets("Selection page").Range("D3").Select``` Still nothing is working as expected. — Marc, Apr 06 '20 at 09:57
@brax the OPs stated objective is to Activate the "next sheet". How do you think avoiding Activate woyld help with that? — chris neilsen, Apr 06 '20 at 10:04
@Marc what runs this code? I suspect you might want the `Worksheet_Change` event. — chris neilsen, Apr 06 '20 at 10:05
@Marc You can use `Activate` in that point, because that's the action you are trying to take. However, you *still haven't fully qualified `Range("D17")`*. Currently it is (implicitly) `ActiveSheet.Range("D17")`, but should it be `Sheet1.Range("D17")` or `ThisWorkbook.Worksheets("Start Page").Range("D17")` or something along those lines instead? Check [this answer](https://stackoverflow.com/a/22080453/9290986) on the [How to avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) page — Chronocidal, Apr 06 '20 at 10:09
@chrisneilsen - there was nothing running the code. I think that was it. I hadn't thought about that. Thanks. I've now amended the code as ```Worksheet.Change``` but receive a "Compile Error: Procedure declaration doesn't match description of event or procedure having the same name." I'd appreciate another push into how to resolve this. Thanks — Marc, Apr 06 '20 at 10:18
@Chronocidal - I've now fully qualified the ```Range``` locations. I thought I didn't have to as this was the sheet's code. — Marc, Apr 06 '20 at 10:19
@Marc If it's code in a Sheet, then you can use the special reference `Me` to refer to the sheet itself as a shortcut (`Me.Range("D17")`) - however, remember that code in a sheet *can* be called from elsewhere, even if the sheet isn't Active. The Event you want is `Private Sub Worksheet_Change(ByVal Target As Range)` - you can use the 2 dropdowns at the top to select "Worksheet" on the left, and "Change" on the right to create this automatically — Chronocidal, Apr 06 '20 at 10:21
@Chronocidal - the ```ByVal Target As Range``` worked brilliantly. Thank you for that. I've no idea what it does, but it works. — Marc, Apr 06 '20 at 10:40
@Marc I'll type it up as an answer (with explanation) so that you can accept it — Chronocidal, Apr 06 '20 at 10:45

score 0 · Accepted Answer · answered Apr 06 '20 at 10:50

Currently, you have nothing triggering the code. You probably want to tie this into a Worksheet event, such as Worksheet_Change or Worksheet_SeletionChange

The quick way to check what Events are available is to go to the top of the Code Pane, select the "Worksheet" object from the Left drop-down, and select the Even from the Right drop-down. This will also automatically add the correct Parameter list:

The "Change" event will occur when any cell on the Worksheet is Changed - while the "SelectionChange" event will happen when you select something new, even if you haven't altered the old thing

For the "Change" event of the Worksheet there is 1 Parameter, ByVal Target As Range - this is the Range of Cells that have been changed. So, for example, you can use MsgBox "Row " & Target.Row & " was changed" to display a message telling you which row the changed cell is on.

Since the Event will automatically pass the Argument to the Sub, you need to have the Parameter there to 'catch' it.

Can't advance to another sheet

1 Answers1