Checking if Sheet Exists?

  • Thread starter Thread starter brett.kaplan
  • Start date Start date
B

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
 
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
 
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
 
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
 
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
 
This did it! Thank you!


Kevin said:
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
Click to expand...
 
Back
Top