script out of range error

Question

Starting a little project but have hit my first wall. When the Workbook opens I would like the code below to run but when it runs, I get a script out of range error and it highlights If Worksheets("Sheet1").Range("O2") = "" Then

Any thoughts on what the issue might be?

Private Sub Workbook_Open()

If Worksheets("Sheet1").Range("O2") = "" Then

MsgBox "hello"

End If

End Sub

Answer 1

Sheet1 is the code name. Its sheet name is June 19. So use either

Private Sub Workbook_Open()

    If Worksheets("June 19").Range("O2") = "" Then

        MsgBox "hello"

    End If

End Sub

or

Private Sub Workbook_Open()

    If Sheet1.Range("O2") = "" Then

        MsgBox "hello"

    End If

End Sub

Answer 2

I do have a sheet1 but am still getting the same error.

Answer 3

To Ordnance1,

Thanks for reaching out.This is Gems88 an Independent Advisor. I'd be more than happy to help you.

I tried to run your code in my excel and I did not encounter any error

Could you please create a new workbook, then go to sheet1,right click,click view code and then right click in the project explorer,then choose insert then choose module,then paste this code

Private Sub Workbooktest()

If Worksheets("Sheet1").Range("O2") = "" Then

MsgBox "hello"

End If

End Sub

If there is anything else I can help you with, please let me know.

Regards,

Gems88

Answer 4

Are you sure that you have a sheet named Sheet1?

If so: sometimes the workbook isn't "ready" yet during Workbook_Open. You might try a small delay:

1. Insert a new module and copy the following code into it.

Sub MyTest()

    If Worksheets("Sheet1").Range("O2") = "" Then

        MsgBox "hello"

    End If

End Sub

2. Change the Workbook_Open event procedure to

Private Sub Workbook_Open()

    Application.OnTime Now + TimeSerial(0, 0, 2), "MyTest"

End Sub

This will run MyTest 2 seconds after the workbook has been opened. If that works, you can try changing 2 to 1 in the code.