It's easy to truncate the name at 30 characters. If the name length
is less than 31 Left returns the name unchanged so this works for
all names.

.Name = Left(myCell.Value, 30)
.Range("B4").Value = .Name

It might be a good idea to Trim that first assignment just in case the
truncated text ends with a space character... no sense leaving the blank on
the end to trip up future references to the sheet name.

..Name = Trim(Left(myCell.Value, 30))

Rick Rothstein (MVP - Excel)