Renaming sheet with reference to cell. Duplicate name proble

vshague

New Member
Joined
Jun 12, 2002
Messages
21
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Jun 12, 2002
Messages
21
Yeah, that worked pretty well...I had to tweak my sheet a bit, but I like your method a lot better.

Thanks.
 

Forum statistics

Threads
1,144,743
Messages
5,726,025
Members
422,654
Latest member
Yashpalknl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top