Renaming sheet from a cell

tugsy

New Member
Joined
Aug 29, 2005
Messages
26
Hi,
I have added some code to rename sheets in a spread sheet when it is being opened.

It works great until I get a name which is longer the 31 characters and then I get an error. (due to the limitation on the sheet name to 31 Characters)

so what I need is for the name to be truncated down to 30 characters, not in the original cell but only when the sheet is being named. I am not a VB coder. This code I found in this forum :rolleyes: and used it in my work sheet.

Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
.Activate
.Name = Range("C8")
End With
Next ws
End Sub


Can any one please help. Thanks.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,
take a look at LEFT in the helpfiles
Code:
Left(Range("C8"), 30)
kind regards,
Erik
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            <SPAN style="color:#00007F">With</SPAN> ws
                .Activate
                <SPAN style="color:#00007F">If</SPAN> .Range("C8") = "" <SPAN style="color:#00007F">Then</SPAN>
                    .Name = .Name
                Else:
                    .Name = Left(.Range("C8"), 30)
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I also took into account if C8 = ""

Hope that helps,

Smitty
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
You're welcome :)
Didn't notice you had 4 posts: so be invited to use the "search" function: see top of the page. This kinda question has been asked many times. You will find useful information to avoid bugs: eg. if sheet already exists, in case of non-valid names.
or google a bit on this site
http://www.google.com/advanced_search?q=site:MrExcel.com

best regards,
Erik
 

tugsy

New Member
Joined
Aug 29, 2005
Messages
26
That works great thanks!
I really Apprecieate your help. this is the second time I have posted, and somthing that would have taken me ages to figure out was solved in under 10 minutes... I love it!

You probably don't get the recognition that all you helpers deserve. Once again Thank you!
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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