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

J

John

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
 
F

Frank Kabel

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
 
J

John

Thanks Frank

Frank Kabel said:
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
Click to expand...
 
J

John

Can't seem to get it to work. I have placed a value in A1 but nothing
happens. How can I initiate it?
 
F

Frank Kabel

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
 
J

John

Got it, but does that mean I have to manually insert this code in to every
new worksheet I create?
 
B

Bob Phillips

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)
 
C

count

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
 
B

Bob Phillips

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

Bob
 
T

TroyW

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
 
J

John

That's Great Bob Thanks


Bob Phillips said:
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)
Click to expand...
 
J

John

Thanks everyone for their help




TroyW said:
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
Click to expand...
 
Top