Naming tabs
 

I would like to have my worksheet named with whatever I enter into a cell
(for example, cell A1, a persons name). I have tried the suggestions I saw
posted and none of them work. Help!!

Naming tabs
 

Try this sub which must be placed on the sheet's module.
You need one for every sheet you which to rename
best wishes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Mike" wrote in message
...
I would like to have my worksheet named with whatever I enter into a cell
(for example, cell A1, a persons name). I have tried the suggestions I saw
posted and none of them work. Help!!

Naming tabs
 

Or place this code into Thisworkbook module and forget about adding to each
sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet
If Not .Range("A1") Is Nothing Then
.Name = Range("A1").Value
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 18:02:53 -0400, "Bernard Liengme"
wrote:

Try this sub which must be placed on the sheet's module.
You need one for every sheet you which to rename
best wishes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub

Naming tabs
 

I have never done a VBA function. Do I just type this in the VBA module and
then it should work, or do I need to do something more?

"Gord Dibben" wrote:

Or place this code into Thisworkbook module and forget about adding to each
sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet
If Not .Range("A1") Is Nothing Then
.Name = Range("A1").Value
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 18:02:53 -0400, "Bernard Liengme"
wrote:

Try this sub which must be placed on the sheet's module.
You need one for every sheet you which to rename
best wishes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub

Naming tabs
 

To add Bernie's code to a sheet, right-click on the sheet tab and "View Code"

Copy/paste his code into the sheet module that appears.

As Bernie notes, you would do this for each sheet you want to automatically
name.

My code covers all sheets in the workbook from one module.

Right-click on the Excel Icon left of "File" on menubar and "View Code".

Copy/paste my code into that Thisworkbook module.

Will work on all sheets in the workbook, including new inserted sheets.

To get a good idea of where to place different types of code, see Ron de Bruin's
site.

http://www.rondebruin.nl/code.htm


Gord


On Tue, 27 Nov 2007 07:19:02 -0800, Mike wrote:

I have never done a VBA function. Do I just type this in the VBA module and
then it should work, or do I need to do something more?

"Gord Dibben" wrote:

Or place this code into Thisworkbook module and forget about adding to each
sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet
If Not .Range("A1") Is Nothing Then
.Name = Range("A1").Value
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 18:02:53 -0400, "Bernard Liengme"
wrote:

Try this sub which must be placed on the sheet's module.
You need one for every sheet you which to rename
best wishes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub

Naming tabs
 

Tried it and it works. Thank you so much!! I need to learn more about Visual
Basic, it would really make life easier. Thanks again.

"Gord Dibben" wrote:

To add Bernie's code to a sheet, right-click on the sheet tab and "View Code"

Copy/paste his code into the sheet module that appears.

As Bernie notes, you would do this for each sheet you want to automatically
name.

My code covers all sheets in the workbook from one module.

Right-click on the Excel Icon left of "File" on menubar and "View Code".

Copy/paste my code into that Thisworkbook module.

Will work on all sheets in the workbook, including new inserted sheets.

To get a good idea of where to place different types of code, see Ron de Bruin's
site.

http://www.rondebruin.nl/code.htm


Gord


On Tue, 27 Nov 2007 07:19:02 -0800, Mike wrote:

I have never done a VBA function. Do I just type this in the VBA module and
then it should work, or do I need to do something more?

"Gord Dibben" wrote:

Or place this code into Thisworkbook module and forget about adding to each
sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet
If Not .Range("A1") Is Nothing Then
.Name = Range("A1").Value
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 18:02:53 -0400, "Bernard Liengme"
wrote:

Try this sub which must be placed on the sheet's module.
You need one for every sheet you which to rename
best wishes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub