Worksheet Names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

veggies27 - 11 Mar 2008 18:47 GMT

Is it possible to name worksheets based on a formula?

I want sheet1 to be renamed to be whatever is in sheet1!A1 for instance.

Reply to this Message

Mike H - 11 Mar 2008 19:02 GMT

Hi,

Right click the sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
End Sub

Mike

> Is it possible to name worksheets based on a formula?
>
> I want sheet1 to be renamed to be whatever is in sheet1!A1 for instance.

Reply to this Message

Dave Peterson - 11 Mar 2008 22:13 GMT

Since it's based on a formula, then maybe using _calculate would be a better
event to use:

Option Explicit
Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Me.Range("a1").Value
If Err.Number <> 0 Then
Beep
MsgBox "Invalid name in A1!"
Err.Clear
End If
On Error GoTo 0
End Sub

> Hi,
>
[quoted text clipped - 10 lines]
> >
> > I want sheet1 to be renamed to be whatever is in sheet1!A1 for instance.

Signature

Dave Peterson

Reply to this Message