Checking if Sheet Exists?

[brett.kaplan]

Hi,

Is there a way in Visual basic to check if a worksheet with a specific
name exists? If it does not exist, I want to add it and format it; if
it does, I just want to format it.

Thanks!

Brett

 

[Guest]

This function return TRUE if the worksheet is found, and FALSE if it isn't.
Just replace the value of the strMatch variable to the name of the worksheet
you're looking for:

Function WrkShtFound() As Boolean

Dim wb As Workbook
Dim ws As Worksheet
Dim strMatch As String
Dim strName As String
Dim blnIsFound As Boolean

On Error GoTo Err_Found

Set wb = ActiveWorkbook
strMatch = "Sheet Name You're Looking For"

For Each ws In wb.Worksheets
strName = ws.name
If strName = strMatch Then
blnIsFound = True
Exit For
Else
blnIsFound = False
End If
Next ws

Exit_Found:

Set wb = Nothing
Set ws = Nothing
WrkShtFound = blnIsFound
Exit Function

Err_Found:

If Err.Number > 0 Then
MsgBox "An error has occurred while attempting to " & _
"verify that this workbook has a worksheet named " _
& strMatch & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & vbCrLf & "Error Description: " & _
Err.Description, vbCritical + vbOKOnly, _
"Error Verifying Worksheet Names"
Err.Clear
blnIsFound = False
Resume Exit_Found
End If

End Function

 

[Guest]

Sub sheetfinder()

Dim wsYourSheet As Worksheet
Dim sSheetName As String

sSheetName = InputBox("sheet name?")
If sSheetName = "" Then Exit Sub

On Error Resume Next
Set wsYourSheet = Worksheets(sSheetName)
On Error GoTo 0

If wsYourSheet Is Nothing Then
Set wsYourSheet = Worksheets.Add
wsYourSheet.Name = sSheetName
Else
Worksheets(sSheetName).Activate
End If

'it is now there and activated and you can put code in here to format it

End Sub

please rate me

 

[Guest]

Sub gsnu()
Dim w As Worksheet
For Each w In Worksheets
If w.Name = "trial" Then
MsgBox ("worksheet " & w.Name & " already exists")
Exit Sub
End If
Next
MsgBox ("worksheet " & "trial" & " does not exist")
End Sub

 

[Dave Peterson]

I like this function from Chip Pearson:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

 

[brett.kaplan]

This did it! Thank you!

This function return TRUE if the worksheet is found, and FALSE if it isn't.
Just replace the value of the strMatch variable to the name of the worksheet
you're looking for:

Function WrkShtFound() As Boolean

Dim wb As Workbook
Dim ws As Worksheet
Dim strMatch As String
Dim strName As String
Dim blnIsFound As Boolean

On Error GoTo Err_Found

Set wb = ActiveWorkbook
strMatch = "Sheet Name You're Looking For"

For Each ws In wb.Worksheets
strName = ws.name
If strName = strMatch Then
blnIsFound = True
Exit For
Else
blnIsFound = False
End If
Next ws

Exit_Found:

Set wb = Nothing
Set ws = Nothing
WrkShtFound = blnIsFound
Exit Function

Err_Found:

If Err.Number > 0 Then
MsgBox "An error has occurred while attempting to " & _
"verify that this workbook has a worksheet named " _
& strMatch & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & vbCrLf & "Error Description: " & _
Err.Description, vbCritical + vbOKOnly, _
"Error Verifying Worksheet Names"
Err.Clear
blnIsFound = False
Resume Exit_Found
End If

End Function