Solved: automatic change of tab names

[RonNCmale]

I have a workbook with 12 sheets: Sheet 1 is named "Staff" and from cells B2 to B8 is a list of names ie John, Ron, Jack on that same sheet. I'm trying to find code that will automatically rename the tabs when the names are changed on Sheet1 to change from sheet 4 to 12 to include default sheet name "sheet" if one of the cells are blank on cell B2 to B8. I'm looking for VBA code and not a macro. Any help will be greatly appreciated.

[patel]

a sample file would be helpful, what's the difference between VBA code and macro ?

[p45cal]

Have a play with this in the Staff sheet's code module:
Private Sub Worksheet_Change(ByVal Target As Range)
Set SensitiveRange = Range("B2:B8")
Set myRange = Intersect(Target, SensitiveRange)
If Not myRange Is Nothing Then
For Each cll In myRange.Cells
If IsEmpty(cll) Then
Sheets(cll.Row + 2).Name = Sheets(cll.Row + 2).CodeName
Else
Sheets(cll.Row + 2).Name = cll.Value
End If
Next cll
End If
End Sub
There are no checks for:
The existence of a sheet before it tries to rename it
The existence of another sheet with the same name.
The presence of characters which aren't allowed in the naming of sheets.
So it will fall over, but it'll get you started.

[RonNCmale]

Thanks p45cal, that code did exactly what I asked. I need to expound on your code a little more. I also need to be able to insert a name into a blank space between B2 and B8 and it rename the tab or if I change a name it automatically changes the tab. As it is now I will have to enter each name again. Also is it possible to have a tab with the same name? As far as characters it will only be names.

[p45cal]

I also need to be able to insert a name into a blank space between B2 and B8 and it rename the tab
It already does this insofar as you enter a name into an existing blank cell.



or if I change a name it automatically changes the tab.It does this too.



As it is now I will have to enter each name again.
It looks for changes in the sheet, to avoid unecessary renaming to the same name of the sheets. If you were to copy/paste in situ it might rename all the sheets, failing that, copy the list of names to somewhere else then copy back to the original location.



Also is it possible to have a tab with the same name?No.

[RonNCmale]

Thanks p45cal, It works great.