PDA

View Full Version : Solved: Error handler in sheet add event



anandbohra
08-18-2007, 12:17 AM
Hi all

I made VBA code to add the sheet based on given text in one text box

do any one have idea as to how to know whether that sheet already exist or not.

If Sheet exists then that gets activated
else system add a new sheet with given name.

Thanks in advance.

p45cal
08-18-2007, 01:25 AM
From Walkenbach:

Private Function SheetExists(sname) As Boolean
' Returns True if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

use for example thus:

Sub test()
MySheetName = "Sheet67"
If SheetExists(MySheetName) Then
Sheets(MySheetName).Activate
Else
Sheets.Add
ActiveSheet.Name = MySheetName
End If
End Sub
p45cal

anandbohra
08-18-2007, 02:01 AM
so nice of you.
Thank you very much.

:friends::hi:

Bob Phillips
08-18-2007, 02:43 AM
An alternative way



On Error Resume Next
Worksheets.Add.Name = "New sheet"
On error goto 0
Worksheets("new sheet").Activate