Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a worksheet exists
Hi all
I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a worksheet exists
Function SheetExists(ByRef SheetName As String) As Boolean
Dim ws As Worksheet SheetExists = False For Each ws In Worksheets If ws.Name = SheetName Then SheetExists = True Next End Function HTH. --Bruce "Mort_Komabt" wrote: Hi all I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a worksheet exists
Thanks for that..... It works great
"bpeltzer" wrote: Function SheetExists(ByRef SheetName As String) As Boolean Dim ws As Worksheet SheetExists = False For Each ws In Worksheets If ws.Name = SheetName Then SheetExists = True Next End Function HTH. --Bruce "Mort_Komabt" wrote: Hi all I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a worksheet exists
Hi Mort,
You could add a new sheet automatically if the value in your named range changes. Try this in the worksheet module of the changing cell. Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim sName As String Dim bShtAdded As Boolean If Target(1).Address = Range("myName").Address Then sName = Range("myName").Value If Len(sName) 1 Then On Error Resume Next Set ws = Worksheets(sName) On Error GoTo errH If ws Is Nothing Then Set ws = Worksheets.Add bShtAdded = True ws.Name = sName End If End If End If Exit Sub errH: If bShtAdded Then MsgBox "Cannot name new sheet : " & sName End If End Sub If your named range is a formla cell, amend the above to check for changes in whatever value cell changes the result in your formula, eg your formula =A! If Target(1).Address = "$A$1" Then If you only want to add a new sheet by calling a normal macro, Sub MyMacro() ' all the code as above but delete If Target(1).Address = Range("myName").Address Then end if End Sub Regards, Peter T "Mort_Komabt" wrote in message ... Hi all I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a worksheet exists
More efficiently
'----------------------------------------------------------------- Function SheetExists(Sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Mort_Komabt" wrote in message ... Thanks for that..... It works great "bpeltzer" wrote: Function SheetExists(ByRef SheetName As String) As Boolean Dim ws As Worksheet SheetExists = False For Each ws In Worksheets If ws.Name = SheetName Then SheetExists = True Next End Function HTH. --Bruce "Mort_Komabt" wrote: Hi all I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check if worksheet exists | Excel Worksheet Functions | |||
check if worksheet exists | Excel Programming | |||
How to check if a worksheet exists in worksheet collection | Excel Programming | |||
Check whether a worksheet exists already | Excel Programming | |||
check if worksheet exists | Excel Programming |