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

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

Thanks Frank

"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

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

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

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

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

Hi
this is an event procedure. There's nothing to run. It should happen
automatically. See
http://www.cpearson.com/excel/events.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
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