Renaming sheet from a cell

[tugsy]

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 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.

 

[erik.van.geit]

Hi,
take a look at LEFT in the helpfiles

Left(Range("C8"), 30)

kind regards,
Erik

 

[Smitty]

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

 

[tugsy]

Thank you Erik and Smitty, I will give it a go.

 

[erik.van.geit]

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]

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!