Clearing target cells:

[Guest]

Hi,

This program works great except for when I clear any cell in the target
range a (Run-Time error ‘1004’: Application-defined or object-defined error)
message pops up. Can this be rewritten to except a blank cell or cell
E5=1,E6=2 and so on when cells are cleared of players names.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E5:E16")) Is Nothing Then
Worksheets(Target.Row - 2).Name = Target.Value
End If

End Sub

Thanks Charles.

 

[Dave Peterson]

There are lots of other things that could go wrong--besides just emptying that
cell. If you put something in that cell that is invalid in a worksheet name
(colon, slash, backslash or even an existing worksheet name), your code will get
sick.

Maybe just trying and catching the error would be better.

Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
if target.cells.count > 1 then exit sub

If Not Intersect(Target, me.Range("E5:E16")) Is Nothing Then
on error resume next 'let it try to rename it
Worksheets(Target.Row - 2).Name = Target.Value
if err.number <> 0 then
msgbox "something bad happened!"
beep
err.clear
end if
on error goto 0
End If

End Sub

 

[Guest]

There are lots of other things that could go wrong--besides just emptying that
cell. If you put something in that cell that is invalid in a worksheet name
(colon, slash, backslash or even an existing worksheet name), your code will get
sick.

Maybe just trying and catching the error would be better.

Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
if target.cells.count > 1 then exit sub

If Not Intersect(Target, me.Range("E5:E16")) Is Nothing Then
on error resume next 'let it try to rename it
Worksheets(Target.Row - 2).Name = Target.Value
if err.number <> 0 then
msgbox "something bad happened!"
beep
err.clear
end if
on error goto 0
End If

End Sub

Dave,

Thank you for fixing my problem and pointing out other existing handicaps
with this program. Its working great now.

Thanks again.

Charles,