Text too long for Tabs

Posted by Kelly on May 24, 2000 5:31 AM

Help!

I've got a Macro that will copy cells in each worksheet and pastes them onto the sheet tab.
This works, however some of the names are too big i.e. over 31 characters (which excel won't allow).

Is there a way that excel can count the text and if containing illegal characters ("/ \ * : [ ] ?")or over 31 spaces can it delete the last characters to equal 31 or under or with illegal characters delete them.

Any little bit of help or other ways around this would be much appeciated.

Kelly

Posted by Aaron :) on May 24, 2000 6:08 AM

Sub StringyDingy()

DirtyString = Selection.Value

x = Len(DirtyString)

For i = 1 To x
n = Asc(Mid(DirtyString, i, 1))
Select Case n
Case 32 'detect a space and replace it with an underscore
CleanString = CleanString & Chr(95)
Case 48 To 57 'allow numbers
CleanString = CleanString & Chr(n)
Case 65 To 90 'allow upper case letters
CleanString = CleanString & Chr(n)
Case 95 'allow underscore character
CleanString = CleanString & Chr(n)
Case 97 To 122 'allow lower case letters
CleanString = CleanString & Chr(n)
End Select
Next i

CleanString = Left(CleanString, 31)
MsgBox CleanString
End Sub

Posted by Kelly on May 24, 2000 6:33 AM

How to use it

x = Len(DirtyString) n = Asc(Mid(DirtyString, i, 1)) Select Case n Case 32 'detect a space and replace it with an underscore CleanString = CleanString & Chr(95) Case 48 To 57 'allow numbers CleanString = CleanString & Chr(n) Case 65 To 90 'allow upper case letters CleanString = CleanString & Chr(n) Case 95 'allow underscore character CleanString = CleanString & Chr(n) Case 97 To 122 'allow lower case letters CleanString = CleanString & Chr(n) End Select Next i CleanString = Left(CleanString, 31) MsgBox CleanString

Sorry for being a bit slow but how do I use this as the message box tells me what it looks like but I need it implemented.

Kel.

Posted by Aaron :) on May 24, 2000 12:13 PM

Re: How to use it

It's a black box.

How you load it and what you do with the output is entirely up to you. I merely shot the output to a msgbox just to show it works.

You put a bad text string in by setting the variable "DirtyString" equal to some cell value(or whatever). You get a result out that's stripped of all the nasties called "CleanString".

Since you didn't post your code I figured you had enough of an understanding of VBA to impelement it on your own.

Post your code and I'll show you how to implement it.

Posted by Kelly on May 25, 2000 12:59 AM

Code attached

How you load it and what you do with the output is entirely up to you. I merely shot the output to a msgbox just to show it works. You put a bad text string in by setting the variable "DirtyString" equal to some cell value(or whatever). You get a result out that's stripped of all the nasties called "CleanString". Since you didn't post your code I figured you had enough of an understanding of VBA to impelement it on your own. Post your code and I'll show you how to implement it.

Aaron,

This is my code:

Windows("New.xls").Activate
Range("A1").Select

Dim k As Integer

Do Until Empty

k = ActiveSheet.Next.Select
ActiveSheet.Select
Range("a5").Select
NewValue = ActiveCell.Value
If ActiveWorkbook.Name = "Happy1.xls" Then End
Selection.Copy
ActiveSheet.Select

ActiveSheet.Name = ActiveCell

For i = 1 To NumberOfSheets
Worksheets(i).Select
SheetNum = CStr(i)
Sheets(i).Name = NewValue + " "
Next i

Loop

End Sub

The problem is when the Macro copies from cell "A5" the cell may be too large etc.

Can you help me out please.

PS I'm new at this Macro game.

Thanxs.

Kel

Posted by Aaron :) on May 25, 2000 4:09 AM

Re: Code attached

Windows("New.xls").Activate
Range("A1").Select

Dim k As Integer

Do Until Empty

k = ActiveSheet.Next.Select
ActiveSheet.Select
Range("a5").Select
NewValue = ActiveCell.Value
If ActiveWorkbook.Name = "Happy1.xls" Then End
Selection.Copy
ActiveSheet.Select

ActiveSheet.Name = CleanString(ActiveCell)

For i = 1 To NumberOfSheets
Worksheets(i).Select
SheetNum = CStr(i)
Sheets(i).Name = CleanString(NewValue + " ")
Next i

Loop

End Sub

Function CleanString(DirtyString As String) As String

x = Len(DirtyString)