Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
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 -- Kevin Backmann " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
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 -- Allllen " wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
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 -- Gary''s Student " wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking if Sheet Exists?
This did it! Thank you!
Kevin B wrote: 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 -- Kevin Backmann " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Asked previously...can this not be done in excel | Excel Discussion (Misc queries) | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Checking if sheet is visible | Excel Discussion (Misc queries) |