Auto-naming worksheets in a workbook under certain conditions

[Luke M]

Here's an example macro that illustrates DoEvents. The macro runs a long counter and changes the value in A1 (so you can see it working) but you can still do some basic operations, like select different cells/worksheets, minimize workbook.

[pre]

Sub ExampleDoEvents()
x = 1
For i = 1 To 10000000

If i Mod 1000 = 0 Then
DoEvents
x = x + 1
Range("A1").Value = x
End If
Next i
End Sub

[/pre]

 

[OleMiss2010]

So I'm not sure if this might be connected to my problem, but what is the purpose of the "SelectionChanged" portion of the code below?

[pre]

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
If Not SelectionChanged Then
ws.Select
SelectionChanged = True
End If
i = 0
CellValue = ws.Range("C5").Value
NewName = CellValue
On Error GoTo ErrHandler
ws.Name = NewName
'Only want to goto error handler if problem
'is here, so we reset the On Error statement
On Error GoTo 0
End If
End If
Next ws

[/pre]

 

[Luke M]

Hmm. I believe that was in there to make sure only 1 sheet got selected. I.e, when it starts renaming sheets, it will select the first one that gets renamed, but won't select any other ones.

 

[OleMiss2010]

So I just figured out where the problem is. If Range("C5") has more characters than a ws.Name will allow, the loop gets an error for which it cannot correct. Is there a simple line of code that I can put in perhaps near NewName = CellValue that will tell it to take only the maximum number of characters? Or do I need to create an ErrHandler that will address this specific issue? What would such a code look like?

 

[OleMiss2010]

For what it's worth, I have a formula that will give me the correct answer:

=Left(C5,Len(C5)-(Len(C5)-28))

This will give me the first 28 characters, which is what I want to use, but I am not sure how to work that into my code.

 

[OleMiss2010]

Solved my own problem. I used a hidden cell with the formula above to extract the first 28 characters from C5 and then made that the reference for the cell name. Worked like a charm.

 

[Luke M]

You can use this code:

[pre]

NewName = Left(CellValue,28)

[/pre]
Nice thing about Left is that if CellValue is less than 28 characters, the whole thing gets passed to NewName, no problems. Otherwise, it gets trimmed.

 

[OleMiss2010]

This was not in my earlier post, but the C5 value was referenced else where in a named range that was driving information in a series of index equations. So I have to have a cell that references the correct name of the tab name in order to make sure the named range works correctly.

 

[Luke M]

C5 needs to display the sheet name?

=MID(CELL("filename",C5),FIND("]",CELL("filename",C5))+1,999)

Putting this anywhere in a sheet will display the sheet name.


*Shouldn't matter in your case, but for later readers, this formula only works w/ a workbook that has been saved already.