Change sheet names based on cell contents

[Jim G]

I have the following code that changes the sheet name based on the entry in
cell A1. This allows the user to create multiple sheet copies of a template
then updateing the data in each without needing to update sheet names as they
go.

Sub UpdateTabName()
Dim Ws As Worksheet
‘ the macro will rename all sheets in the active workbook to the contents of
A1

With ActiveWorkbook

For Each Ws In Worksheets
Ws.Name = Ws.Range("A1") ‘ change to the cell address to be used
Next Ws

End With

End Sub

I would like to insert a error trap and message for when the target cell is
blank or has illegal characters. Ideally, I would like to provide a means
for the user to be prompted for a new name for that sheet. If that is not
possible or practical, insert a temporary name and alert the user to change
it.

Any suggestions?

 

[Barb Reinhardt]

You could try something like this

For Each Ws In .Worksheets
On Error Resume next
Ws.Name = Ws.Range("A1") ‘ change to the cell address to be used
If Err <> 0 then
WS.Select
on error resume next
WS.Name = InputBox("Enter Worksheet Name",WSName)
if err <> 0 then Msgbox ("Unable to set worksheet name.")
end if

 

[keiji kounoike]

Try this one.

Sub UpdateTabName()
Dim Ws As Worksheet
Dim n

With ActiveWorkbook
For Each Ws In Worksheets
On Error GoTo ex:
Ws.Select
Ws.Name = Ws.Range("A1").Text
Ignore:
Next Ws

End With
Exit Sub
ex:
n = Application.InputBox("Wrong Sheet'S Name!!", _
Default:=Ws.Range("A1").Text, Type:=2)
If VarType(n) = vbBoolean Then
Resume Ignore:
End If
Ws.Range("A1") = n
Resume
End Sub

Keiji

 

[keiji kounoike]

Sorry Barb for wrong post.
This was intended to Jim's post, Message Id:
(e-mail address removed)>

Keiji

 

[Jim G]

Thanks Barb, worked great. I was going to ask for the cell reference to be
updated with the new name, but Keiji beat me to it.

Cheers

 

[Jim G]

Brilliant! You anticipated my every need.

Thanks Heaps.

Cheers

 

[keiji kounoike]

You're welcome.

Keiji