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
|