Renaming sheet with reference to cell. Duplicate name proble

[vshague]

I pulled the guts of this little snippit from this forum and it works fine, but if I have two worksheets that result in the same label, there is an error. How would one go about writing it so that if a matching name exists, the new name would be something like 'name(2)'?

Also, since technically, this snippit is being called every time a selection is being made is there an easy way to optimize?

Thanks.

>Private Sub Worksheet_SelectionChange(ByVal >Target As Excel.Range)
>ActiveSheet.Name = ActiveSheet.Range("c3")
>End Sub
>
>This will rename the active sheet to whatever is in cell A1. Is that what you want?
This message was edited by vshague on 2002-10-09 15:34

 

[lenze]

Ok: First I would consider using the WorkSheet Activate event or the Change Event
This will keep the code from firing each time a new cell is selected. As to the problem of duplicate names, I'm sure someone may have a better idea, but just playing with it I came up with

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim myName As String
myName = ActiveSheet.Range("$A$1").Value
On Error GoTo Errortrap
ActiveSheet.Name = myName
Exit Sub
Errortrap:
ActiveSheet.Name = myName & "2"
End Sub

I'm sure you will have to modify it, but maybe this will get you started
This message was edited by lenze on 2002-10-09 15:55

 

[vshague]

Yeah, that worked pretty well...I had to tweak my sheet a bit, but I like your method a lot better.

Thanks.