Cameron

Me.Name = Target.Value & "apples"

Gord Dibben Excel MVP - XL97 SR2 & XL2002


On Wed, 30 Jul 2003 08:17:17 -0700, "Cameron"
wrote:

I recently was given a formula by a kind user that sets a
worksheet tab name to a cell value. I'd like to refine
this. It doesn't work if the cell value is the result of a
formula. Furthermore, I'd like to add some text to it.

i.e.

In work sheet 2, cell c4 references the value of a cell in
sheet1 (=sheet1!a1)

if a1 has 4 entered in , the sheet2!C3 will equal 4
which in turn will set the tab name to 4. I'd like to add
the "apples" to the result so that the tab name reads 4
apples. (perhaps a silly example but explanatory)

The original formula give to me:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "A1" Then
On Error Resume Next
Me.Name = Target.Value
On Error GoTo 0
If Me.Name < Target.Value Then _
MsgBox Target.Value & _
" is not a valid sheet name or is a
duplicate"
End If
End Sub


Any suggestions??

Thanks