MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sheet Names

Posted by Spence Baker on January 28, 2002 12:59 PM

Is there a way to assign the sheet name as a reference to a cell?

Thanks for your help.

Spence Baker

Posted by chief on January 28, 2002 3:14 PM

If you are referencing to a cell on another worksheet other than the sheet the formula is on put the worksheet then the cell

On sheet one in any cell put fomula =SUM(Sheet2!A1)
What ever is in sheet 2, cell A1 will be the answer.

Posted by Poins on January 28, 2002 4:30 PM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCell As Range, check As String
Set theCell = Range("A1")
Application.EnableEvents = False
If Not Intersect(theCell, Target) Is Nothing Then
check = MsgBox("Do you want this sheet named as the entry in " _
& theCell.Address(False, False) & "?", vbYesNo)
If check = vbNo Then
theCell.Value = ActiveSheet.Name
Application.EnableEvents = True
Exit Sub
End If
End If
On Error Resume Next
ActiveSheet.Name = theCell.Value
If Err.Number <> 0 Then
GoTo ErrH
End If
On Error GoTo 0
Application.EnableEvents = True
Exit Sub
MsgBox "The data that was input to " _
& theCell.Address(False, False) _
& " is not a valid name for a worksheet." _
& " Input another name."
theCell.Value = ActiveSheet.Name
Application.EnableEvents = True
End Sub