How to add a worksheet with the value of the cell I double clicked

Question

I developed this code to select a worksheet named with the text of a cell on column B when I double click on it. But I am trying to increase my code with one IF condition: If there is no worksheet named with the cell I double clicked show a msgbox ("Hello") and after that create the worksheet with the name of the cell I double clicked.

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 2 Then

If Sheets(Target.Text) = True Then
Sheets(Target.Text).Visible = xlSheetVisible
Sheets(Target.Text).Select
Sheets(Target.Text).Range("A2").Select

Cancel = True
Else
MsgBox ("hello")

End If
End If

End Sub

This condition does not work as I thought If Sheets(Target.Text) = True Then

Possible duplicate of [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — sourceCode, Jul 20 '17 at 16:21

score 0 · Accepted Answer · answered Jul 20 '17 at 14:32

Sheets(Target.Text) is a Worksheet-object, you cannot compare it to a boolean. Furthermore, if the sheet doesn't exists, you will get a runtime error. What you want is a function to check if a worksheet exists. You can find many examples, for example on stackoverflow

Copy such a function into your code and change your event handler

If SheetExists(Target.Text) Then
    Sheets(Target.Text).Visible = xlSheetVisible
    Sheets(Target.Text).Select
    Sheets(Target.Text).Range("A2").Select
    Cancel = True
Else
    MsgBox ("hello")
End If

How to add a worksheet with the value of the cell I double clicked

1 Answers1