当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - vba check if sheet exists - object required error

问题描述:

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: