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

thanx Gord. I've been playing around with it and I'm
trying to get the tab to default to "XXX apples" if the
formula result is 0 or the cell is blank
-----Original Message-----
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

.