Hi

I'm building a sales tracker for my company and need to create a function
where the "administrator" can rename a specific range of tabs according to a
list of staff by clicking on a button.

There are ten "staff" tabs (named CA1 through CA10) which i need to control
by the manager keying the name of the member of staff into a sheet called
Staff Details and then pressing a button to refresh the names. I simply
cannot figure out how to rename a sheet according to a central reference...
anything i try will not work!

Hi

In Excel2003 I have created this:

Private Sub cmdRename_Click()
Dim strOldName As String
Dim strNewName As String
Dim intRowLoop As Integer
Dim shtRename As Worksheet

intRowLoop = 2
Do
strOldName = Cells(intRowLoop, 1).Value
If Not IsEmpty(Cells(intRowLoop, 2)) Then
strNewName = Cells(intRowLoop, 2).Value
For Each shtRename In ThisWorkbook.Worksheets
If shtRename.Name = strOldName Then
shtRename.Name = strNewName
Cells(intRowLoop, 1).Value = strNewName
Cells(intRowLoop, 2).ClearContents
End If
Next
intRowLoop = intRowLoop + 1
End If
Loop Until IsEmpty(Cells(intRowLoop, 1))
End Sub

It expects on a control sheet the staff tabname CA1, CA2, ...
and next to it the names of the staf members.
It loops throu the names and checks if there is a new name next to it
if so, looks for tan tab with this name.
If it is found the name of the tab is changed as if the old name on
the control.
Also is the old name on the control sheet replaced and de new name
deleted.
If a staffmember at some point in the future is replaced this macro
van be used to change the apporpiate tabname.
If a new stafmember is added to the department it will stil work.

HTH,

Wouter

You have ten worksheets named CA1 thru CA10
The manager will type a staff name into the worksheet Staff Details
Let's say he types "George"
Now what do you want to happen?
I may not be the one to answer the question, but as posed it is somewhat
unclear.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"teafan" wrote in message
...
Hi

I'm building a sales tracker for my company and need to create a function
where the "administrator" can rename a specific range of tabs according to
a
list of staff by clicking on a button.

There are ten "staff" tabs (named CA1 through CA10) which i need to
control
by the manager keying the name of the member of staff into a sheet called
Staff Details and then pressing a button to refresh the names. I simply
cannot figure out how to rename a sheet according to a central
reference...
anything i try will not work!

sorry, the code should only work when the manager presses the button - so if
we had a list of advisers: Simon, Daryl, Jakub, Barry and then George started
work, the manager would type George into the cell below Barry and click a
button. This would then rename the appropriate tab and that's it! There are
always ten CA tabs - never any more or less (i use a macro to hide these if
they are unused).

"Bernard Liengme" wrote:

You have ten worksheets named CA1 thru CA10
The manager will type a staff name into the worksheet Staff Details
Let's say he types "George"
Now what do you want to happen?
I may not be the one to answer the question, but as posed it is somewhat
unclear.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"teafan" wrote in message
...
Hi

I'm building a sales tracker for my company and need to create a function
where the "administrator" can rename a specific range of tabs according to
a
list of staff by clicking on a button.

There are ten "staff" tabs (named CA1 through CA10) which i need to
control
by the manager keying the name of the member of staff into a sheet called
Staff Details and then pressing a button to refresh the names. I simply
cannot figure out how to rename a sheet according to a central
reference...
anything i try will not work!