Worsheet Name Change Based on cell's value

N

nuver

Hello
I want to automatically change the name of a worksheet based on th
value of cell K5 when the value of cell K5 changes. I currently hav
cell K5 refering to the value of a cell in another Worksheet. Is ther
a way to automatically rename a sheet based on the formula result of
cell within that worksheet?

Thank you
Edwar
 
F

Frank Kabel

Hi
try the following macro. Put this in your worksheet module (not in a
standard module):
Private Sub Worksheet_Calculate()
Static old_cell As Variant

On Error GoTo errhandler
If IsEmpty(old_cell) Then
old_cell = Me.Range("K5").Value
End If

With Me.Range("K5")
If .Value <> old_cell Then
Application.EnableEvents = False
Me.Name = .Value
old_cell = .Value
End If
End With

errhandler:
Application.EnableEvents = True
End Sub
 
S

steveB

You can use the worksheet calculate event:
Note that I had to qualify the sheet with an index number.
Also made provision if K5 = 0

''''''''''''''''''''''
Private Sub Worksheet_Calculate()
If Sheets(3).Range("K5") <> 0 Then
If Sheets(3).Name <> Sheets(3).Range("K5") Then
Sheets(3).Name = Range("K5")
End If
End If
End Sub
'''''''''''''''''''''''''
hth
 
F

Frank Kabel

Hi
you should disable event before changing the worksheet name as this
could cause also a new worksheet_claculate event
 
N

nuver

Thank you Frank but I could not get the code to work. I pasted your cod
in the Worsheet module but it does not change the name of th
worksheet.

Steve I pasted your code and it did work for the 3rd sheet in th
workbook so I simply changed the index number to 2 and it worked on th
sheet I was trying to change. If I make a copy of the sheet then I nee
to go into the code and manually change the index, is there a way t
make this automatic when I creat a copy of the worksheet?

Thank you both for your advice and quick response
 
S

steveB

Frank,

Thanks! Didn't include that since the code was looking to see if the name
needed changing and it all happens pretty fast. But it would seem to be
good form to include the disable.
 
F

Frank Kabel

Hi
you have to paste this code into the worksheet for which the name has
to be changed. It checks cell K5 on this sheet
 
S

steveB

You could use the Code name of the sheet. This only gets changed from VB
Editor (or in code) - it is not the name on the sheet tab (this code only
changes the name on the tab). Just check the VB Editor to get the correct
code name.

But now you have reached my ceiling. I know that ThisWorkbook refers to the
workbook containing the code. Not sure what the eqivalent is for ThisSheet
(not found).

Maybe Frank can give us a clue of how to deal with this when you copy the
worksheet.

''''''''''''''''''''
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

If Sheet2.Range("K5") <> 0 Then
If Sheet2.Name <> Sheet2.Range("K5") Then
Sheet2.Name = Range("K5")
End If
End If

Application.EnableEvents = True
End Sub
'''''''''''''''''''''''''''''

hth
 
F

Frank Kabel

Hi
within a worksheet event simply use
me
this refers to the sheet the worksheet event resides in.
e.g.
sub worksheet_calculate()
msgbox me.name
end sub
 
N

nuver

Thank you both for all of your help. I am getting closer to getting thi
code to work but this is my first attempt working with code.
I tried the code below replacing Sheet2. with me. but now I can not ge
it to work at all. When you get a chance could you please let me kno
what I am doing wrong. Sorry to be such a pest.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

If Me.Range("K5") <> 0 Then
If Me.Name <> Me.Range("K5") Then
Me.Name = Range("K5")
End If
End If

Application.EnableEvents = True
End Su
 
T

Tom Ogilvy

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

On Error Resume Next
Me.Name = me.Range("K5").Value

Application.EnableEvents = True
End Sub
 
Top