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?

--
Jim

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
on error goto 0
Next Ws

"Jim G" wrote:

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?

--
Jim

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
Igno
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 Igno
End If
Ws.Range("A1") = n
Resume
End Sub

Keiji

Barb Reinhardt wrote:
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
on error goto 0
Next Ws

"Jim G" wrote:

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?

--
Jim

Sorry Barb for wrong post.
This was intended to Jim's post, Message Id:


Keiji

keiji kounoike wrote:
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
Igno
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 Igno
End If
Ws.Range("A1") = n
Resume
End Sub

Keiji

Barb Reinhardt wrote:
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
on error goto 0 Next Ws
"Jim G" wrote:

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?

--
Jim

Brilliant! You anticipated my every need.

Thanks Heaps.

Cheers
--
Jim


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

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
Igno
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 Igno
End If
Ws.Range("A1") = n
Resume
End Sub

Keiji

Barb Reinhardt wrote:
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
on error goto 0
Next Ws

"Jim G" wrote:

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?

--
Jim

You're welcome.

Keiji

Jim G wrote:
Brilliant! You anticipated my every need.

Thanks Heaps.

Cheers

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


"Barb Reinhardt" wrote:

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
on error goto 0
Next Ws

"Jim G" wrote:

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?

--
Jim