Judy Deo
asked on
How to edit the macro code to limit the number of characters when creating worksheet tab name
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
thx Roy will try that as well!
ASKER