VBA Example – Check if a sheet exists in workbook

Private Sub CommandButton1_Click()

Dim sht_name As String

sht_name = Application.InputBox(“enter name”, Type:=2)

‘create a function called check_sheetname in module
If check_sheetname(sht_name) = True Then
MsgBox “exists”

Else

MsgBox “does not exist”

End If
End Sub

create function in module

Public Function check_sheetname(sht As String) As Boolean

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

If ws.name = sht Then

check_sheetname = True

Else

chck_sheetname = False

End If
Next ws

End Function

Leave a Reply