Renaming Worksheet Error

[bry32]

I have a list of projects and each has a button with it. When the user
clicks the button the macro copies a hidden "form" worksheet and names the
new worksheet the name of the project.

The Macro works fine unless the button has already been used. Can't have two
worksheets with the same name.

I've tried the On Error command and a Err.Description with no luck.

Below is the Macro. If you have projects in the "list" worksheet and the
first project in Cell A3 and a hidden worksheet, "form", the Macro will work
the first time. The second time it's run is returns a run time error at the
Active.Sheet = mSheetName line. If it receives an error here I would like to
delete the new sheet and exit the sub.

Thanks for any help.

Sub Button4_Click()

Dim mSheetName As String

mSheetName = Cells(3, 1)
Sheets("Form").Visible = True
Sheets("Form").Select
Sheets("Form").Copy Before:=Sheets("Form")
Sheets("Form (2)").Select
Cells(4, 2) = mSheetName
ActiveSheet.Name = mSheetName
Sheets("Form").Visible = False
Sheets("List").Select

End Sub

[sebastienm]

Hi,
You may wantr to check if the sheet already exists at the begining of the
sub (before even copying the new sheet)

Dim mSheetName As String
Dim Wsh as Worksheet

mSheetName = Cells(3, 1)
on error resume next
set wsh=worksheets(mSheetName)
if err=0 then 'no error ie exist
Msgbox "Sheet name already exist. Please choose another name"
Exit sub
End if
on error goto 0

Sheets("Form").Visible = True
Sheets("Form").Select
Sheets("Form").Copy Before:=Sheets("Form")
Sheets("Form (2)").Select
Cells(4, 2) = mSheetName
ActiveSheet.Name = mSheetName
Sheets("Form").Visible = False
Sheets("List").Select

End Sub

Regards,
Sebastien

"bry32" wrote:

> I have a list of projects and each has a button with it. When the user
> clicks the button the macro copies a hidden "form" worksheet and names the
> new worksheet the name of the project.
>
> The Macro works fine unless the button has already been used. Can't have two
> worksheets with the same name.
>
> I've tried the On Error command and a Err.Description with no luck.
>
> Below is the Macro. If you have projects in the "list" worksheet and the
> first project in Cell A3 and a hidden worksheet, "form", the Macro will work
> the first time. The second time it's run is returns a run time error at the
> Active.Sheet = mSheetName line. If it receives an error here I would like to
> delete the new sheet and exit the sub.
>
> Thanks for any help.
>
> Sub Button4_Click()
>
> Dim mSheetName As String
>
> mSheetName = Cells(3, 1)
> Sheets("Form").Visible = True
> Sheets("Form").Select
> Sheets("Form").Copy Before:=Sheets("Form")
> Sheets("Form (2)").Select
> Cells(4, 2) = mSheetName
> ActiveSheet.Name = mSheetName
> Sheets("Form").Visible = False
> Sheets("List").Select
>
> End Sub
>