Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Exceeding Characters
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Exceeding Characters
Resolved · Urgent Priority · Version 2003
Reply Mon 14 Apr 2008, 15:58Delegate Rebecca said...
Rebecca has attended:
Excel Advanced course
Exceeding Characters
Hi,
I have written a code that looks at a specific cell reference in a datasheet.
The lines that contain the specific cell reference are then moved to their own individual worksheet within the same workbook.
The new worksheet is then re-named as the cell reference, however some names exceed the 31 characters allowed to rename a worksheet.
Can you tell me how to resolve the problem
Many thanks
Becky
For upcoming training course dates see: Pricing & availability
RE: Exceeding Characters
Hi Rebecca
Thank you for your question
First let me aplologise for the delay in replying to your question. We have had very high volumes of posts and as a result there have been a number of delays in responding questions.
Assuming you haven't already resolved the issue, can I suggest you email me a copy of the workbook to stephenATmicrosofttraining.net and I will consider the problem as a matter of urgency
Regards
Stephen
Reply Thu 22 May 2008, 15:42Delegate Rebecca said...
RE: Exceeding Characters
Good afternoon Stephen,
Many thanks for your help with my code regarding excedding the maimum name length for worksheets. I have used the code you sent me which is naming the worksheets within the characters permitted, however from the "C&1" report isn't coming across, the sheet just remains blank and I can't find the problem with it!!!
Would it be possible to help me anymore on this one??
Many thanks
Becky
RE: Exceeding Characters
Hi Rebecca
Thanks for your question and for the file
I have looked at the problem, and the first thing to state is that you cannot exceed the maximum name length and therefore you need a strategy for naming your sheets. As any company name that exceeds the maximum length will comprise two or more words, I suggest just using the first word of the name as the sheet title. For example "Best Training", would become be titled "Best".
I have modified your AskForSupplier procedure as follows
Sub AskForSupplier()
Dim intLength As Integer
Dim strTitle As String
Call DeleteSheetIfExists
Worksheets.Add after:=Worksheets(Worksheets.Count)
intLength = Application.WorksheetFunction.Find(" ", txtSupplier)
strTitle = Left(txtSupplier, intLength)
ActiveSheet.Name = strTitle
Sheets("C&I").Select
End Sub
The strategy is to use the worksheet function FIND to locate the first space in the supplier name, and then to use this in the VBA function LEFT, to trim off the first name and set it equal to the variable strTitle.
Hope this is useful
If this strategy is not suitable please let me know so we can explore other options
Regards
Stephen
|