Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to edit the macro code to limit the number of characters when creating worksheet tab name

Posted on 2016-10-12
5
Medium Priority
?
79 Views
Last Modified: 2016-10-16
Hi, on a previous Experts-Exchange (https://www.experts-exchange.com/questions/28972223/How-to-do-this-in-Microsoft-Excel.html) we got a great solution to create the attached file.

On a few of the worksheets tab names though that get automatically created, it was saying "Sheet1", "Sheet 2", etc. We realize that it was because there is a 31 character limit for worksheet tab names and the code didn't account for this. Our bad since we didn't realize that.

Anyways, we're looking to alter the code we received in the previous post and that's in the attached file so it limits the tab names to the first 31 characters so that Excel doesn't receive an error and put ex. "Sheet 1" instead.
VBA_Excel_-Test_FileQ28972223-Rev-1.xlsm
0
Comment
Question by:Judy Deo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41841191
Change

ws.Name = Mid(cel.Value, 17)

to

ws.Name = Left(Mid(cel.Value, 17), 31)
0
 

Author Closing Comment

by:Judy Deo
ID: 41841323
thank you
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41841335
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41841390
This example code includes a Function that tests for any bad sheet names - characters, length, etc.
Option Explicit
Sub Copy_Rename()
     ' Copy_Rename Macro
    Dim strName As String, strSht As String
     '======Test if sheet exists usinfg Function and name is correct format
         strName = Range("C4").Value
    If Not BadName(strName) Then
'    On Error Resume Next
    If Not WksExists(strName) Then
        strSht = ActiveSheet.Name
       Sheets.Add After:=Sheets(Sheets.Count)
       ActiveSheet.Name = strName
        Sheets(strSht).Range("A:M").Copy Sheets(strName).Range("A1")
    Sheets(strSht).Range("C4:D9, F4:L5, F7:L7, I9:L9, B11:H36, B39:L49").ClearContents
     Else: MsgBox "The number you have entered has already been used." & vbNewLine & "This data will not be saved."
     Exit Sub
     End If
     Else: MsgBox "The name for the sheet is not in the correct format" & vbNewLine & "This data will not be saved."
     End If
End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Function BadName(s As String) As Boolean
    BadName = False
    Dim iBadCharsCount As Integer
    iBadCharsCount = InStr(1, s, ":") + InStr(1, s, "\") + InStr(1, s, "/") + _
                     InStr(1, s, "?") + InStr(1, s, "*") + InStr(1, s, "[") + InStr(1, s, "]")
    
    If iBadCharsCount > 0 Or Len(s) > 31 Then
        BadName = True
    End If
End Function

Open in new window

0
 

Author Comment

by:Judy Deo
ID: 41845629
thx Roy will try that as well!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question