Thanks everyone for their help




"TroyW" wrote in message
...
John

Here is an alternate way to provide Events for multiple sheets. You can
use
a Class Module to sink events for the entire workbook. This avoids having
to
add the event code to each individual worksheet from an external file. You
simply define the code once in a Class Module for the entire workbook. All
of the code is contained within the workbook.

I added a MsgBox to Frank's original code in case the user types an
illegal
worksheet name to indicate an error condition.

Below is the 4-Step process to using the Class Module procedure.

A---Create the Class Module:
- In the VBA Editor, create a Class Module using the Insert | Class Module
command from the menubar
- Press the F4 button to show the Properties dialog box
- Change the entry in the (Name) field from Class1 to XLAppClass
- Close the Properties dialog box

B---Add the Class Module code:
- Place the following code in the current XLAppClass code pane
'''=============================================== ==========
Option Explicit

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Sh.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Sh.Name = .Value
End If
End With
Application.EnableEvents = True

Exit Sub
CleanUp:
MsgBox "Error: Could not rename the worksheet to: " & Target.Value
Application.EnableEvents = True

End Sub
'''=============================================== ==========


C---Define the ThisWorkbook Code:
- Place the following code in the ThisWorkbook code pane
'''=============================================== ==========
Option Explicit

Dim myXLAppClass As New XLAppClass

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set myXLAppClass = Nothing
End Sub

Private Sub Workbook_Open()
Set myXLAppClass.xlApp = Excel.Application
End Sub
'''=============================================== ==========


D---Save, Exit, and ReOpen the Workbook:
- Save the workbook, close the workbook, and then reopen it (click Enable
Macros).

Each time the value in cell A1 is changed on any worksheet in the given
workbook, the worksheet will rename itself to the value in the cell A1
(provided it is a legal name). Worksheets can be added by the user to the
workbook and the code will automatically apply to the new worksheet.

Troy


"John" wrote in message
...
Got it, but does that mean I have to manually insert this code in to
every
new worksheet I create?


"John" wrote in message
...
Yeah it was in there Frank but I don't see any macro to run - should I


"Frank Kabel" wrote in message
...
Hi
you have to put this code in your worksheet module 8not in a
standard
module). Right-click on your tab name, choose 'code' and isert the
code
in the appearing VBA editor

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Can't seem to get it to work. I have placed a value in A1 but
nothing
happens. How can I initiate it?

"Frank Kabel" wrote in message
...
Hi
Put the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
If .Value < "" Then
Me.Name = .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
I want to created a new worksheet via a macro button on say
sheet1 -
I can
do that thats not a problem

Then I want to re-name the new worksheet with a value that is
typed
in on a
certain cell in this new sheet2. Obviously I can't re-name
until
I
create
the worksheet in the first place. How can I create an event to
do
this after
I have typed in to this cell.

I'm trying to set up a holiday timesheet, with a new sheet for
each
employee

Thanks