vba check if sheet exists - object required error

Question

I'm working on this code that when user enter value in the target range, the program will check if sheet name exists, and react accordingly.

I have the following code, the second part(Cell C17) works fine, but this line in the first part(Cell C3) If Not Sheet Is Nothing Then throws a object required error. I look at the code in the debug mode, and found the value of sheet is empty while sheet1 is nothing. The logic for the two parts are exactly the same, so I'm confused about why the first cell doesn't work. Could anyone point it out for me please? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rp, rp1 As String
Dim Sheet, Sheet1 As Worksheet

    rp = ThisWorkbook.Sheets("Settings and Instruction").Range("C3").Value
    rp1 = ThisWorkbook.Sheets("Settings and Instruction").Range("C17").Value


    On Error Resume Next
    Set Sheet1 = Worksheets(rp1)
    On Error GoTo 0

    On Error Resume Next
    Set Sheet = Worksheets(rp)
    On Error GoTo 0

    If Target.Address = "$C$3" Then


            If Not Sheet Is Nothing Then

            MsgBox "Sheet name already exists, please enter a new period."
            Else
            ConfirmPeriodNew.Show
            End If


    ElseIf Target.Address = "$C$17" Then

            If Not Sheet1 Is Nothing Then
            ConfirmPeriodUp.Show
            Else
            MsgBox "The period you've entered doesn't exist, please double check"
            End If

    End If

End Sub

I haven't test your code yet but by just seeing the above, remove your "on error resume next", that line basically hides your potential errors in the code. Validate your value in C3 (test by copying value from C17). Also I would use "sht" instead of "sheet" as variable name to avoid confusion — Alex
– Alex, Commented Apr 6, 2015 at 19:58
Sheet was not defined as a worksheet. The code "Dim Sheet, Sheet1 As Worksheet" is only setting Sheet1 to a worksheet type. You need "Dim Sheet As Worksheet, Sheet1 As Worksheet." — OpiesDad
– OpiesDad, Commented Apr 6, 2015 at 20:39

OpiesDad · Accepted Answer · 2015-04-06 20:45:25Z

3

The problem is as I mentioned in my comment. Sheet is defined as a variant, and so when you run the code, it is not set as an object. Setting "Sheet" is causing an error (as the worksheet doesn't exist), but since the type of Sheet is variant, its value is Empty instead of the Nothing you are later checking for.

Dim rp As String, rp1 As String
Dim Sheet As Worksheet, Sheet1 As Worksheet

This will fix the issue you are having.

answered Apr 6, 2015 at 20:45

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qshng Over a year ago

Thanks a lot! Looks like I've been wrong with the way to define variables for a while..

Collectives™ on Stack Overflow

vba check if sheet exists - object required error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related