Hi njws,
The answer to both questions is yes. For the first question, the following user-defined function (UDF) will yield the worksheet name. This function can be placed in a standard macro module.
Function SheetName() As String
' Returns the name of the worksheet from which the function is called
SheetName = Application.Caller.Parent.Name
Application.Volatile
End Function
To install this code, use keyboard Alt-TMV to go to the Visual Basic Editor, Alt-IM to insert a new macro module, and paste the code into the Code Pane.
The second question requires an event macro. As an example the following macro will rename the sheet to whatever string is entered into cell B4:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Name = [b4].Text
End If
End Sub
Because it is an event macro it must be placed in the event code module of the worksheet. To do this, right-click on the worksheet's tab, select View Code, and paste the code into the Code Pane.