Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

John,
This can be automated I suppose - read this from Chip Pearson's treasure
box:
http://www.cpearson.com/excel/vbe.htm
hth
Paul

U¿ytkownik "John" napisa³ w wiadomo¶ci
...
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

Re-Name a Worksheet..... with a Twist
 

No it doesn't.

Put the code into a text file and save it somewhere on your system.

Then add this code to your code that creates the new sheet and call it after
the sheet is created

'---------------------------------------------------------------------
Public Sub AddCode()
'---------------------------------------------------------------------
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents
With VBComps(ActiveSheet.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name) _
.CodeModule.AddFromFile "c:\myTest\code.txt"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

John,
Just an idea:
By when you need this new name to exist?
If by Workbook Close / Save time then you could do without sheet events.
Just catch the number / names of sheets on Open and do a stocktake on exit
.... rename accordingly, perhaps mere parsing on Close for names such as
Sheet1 will do the trick.
HTH even more :)
Paul

U¿ytkownik "John" napisa³ w wiadomo¶ci
...
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

Re-Name a Worksheet..... with a Twist
 

John,

You could add the code automatically. See Chip Pearson's instructions for
Adding a Module to a Workbook at http://www.cpearson.com/excel/vbe.htm

hth,

Doug Glancy

"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

Re-Name a Worksheet..... with a Twist
 

Hi Bob
and I should stay out of this NG :-)


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
No it doesn't.

Put the code into a text file and save it somewhere on your system.

Then add this code to your code that creates the new sheet and call
it after
the sheet is created


'---------------------------------------------------------------------
Public Sub AddCode()

'---------------------------------------------------------------------
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents
With VBComps(ActiveSheet.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name) _
.CodeModule.AddFromFile "c:\myTest\code.txt"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

This is exactly what I have been doing on our project, so I was right on top
of it.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
and I should stay out of this NG :-)


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
No it doesn't.

Put the code into a text file and save it somewhere on your system.

Then add this code to your code that creates the new sheet and call
it after
the sheet is created


'---------------------------------------------------------------------
Public Sub AddCode()

'---------------------------------------------------------------------
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents
With VBComps(ActiveSheet.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name) _
.CodeModule.AddFromFile "c:\myTest\code.txt"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

I just skipped through your code: Great!

--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
This is exactly what I have been doing on our project, so I was right
on top
of it.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
and I should stay out of this NG :-)


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
No it doesn't.

Put the code into a text file and save it somewhere on your
system.

Then add this code to your code that creates the new sheet and
call
it after
the sheet is created



'---------------------------------------------------------------------
Public Sub AddCode()


'---------------------------------------------------------------------
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents
With VBComps(ActiveSheet.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name) _
.CodeModule.AddFromFile "c:\myTest\code.txt"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

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

Re-Name a Worksheet..... with a Twist
 

That's Great Bob Thanks


"Bob Phillips" wrote in message
...
No it doesn't.

Put the code into a text file and save it somewhere on your system.

Then add this code to your code that creates the new sheet and call it
after
the sheet is created

'---------------------------------------------------------------------
Public Sub AddCode()
'---------------------------------------------------------------------
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents
With VBComps(ActiveSheet.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name) _
.CodeModule.AddFromFile "c:\myTest\code.txt"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi John
yes it does

--
Regards
Frank Kabel
Frankfurt, Germany

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

Re-Name a Worksheet..... with a Twist
 

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