Naming tabs

[Mike]

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!!

 

[Bernard Liengme]

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

 

[Gord Dibben]

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

 

[Mike]

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]

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

 

[Mike]

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.