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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


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

Thank you
Edward


---
Message posted from http://www.ExcelForum.com/

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

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"nuver " wrote in message
...
Hello
I want to automatically change the name of a worksheet based on the
value of cell K5 when the value of cell K5 changes. I currently have
cell K5 refering to the value of a cell in another Worksheet. Is there
a way to automatically rename a sheet based on the formula result of a
cell within that worksheet?

Thank you
Edward


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany


steveB wrote:
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

"nuver " wrote in message
...
Hello
I want to automatically change the name of a worksheet based on the
value of cell K5 when the value of cell K5 changes. I currently have
cell K5 refering to the value of a cell in another Worksheet. Is
there a way to automatically rename a sheet based on the formula
result of a cell within that worksheet?

Thank you
Edward


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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.

--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"Frank Kabel" wrote in message
...
Hi
you should disable event before changing the worksheet name as this
could cause also a new worksheet_claculate event

--
Regards
Frank Kabel
Frankfurt, Germany


steveB wrote:
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

"nuver " wrote in message
...
Hello
I want to automatically change the name of a worksheet based on the
value of cell K5 when the value of cell K5 changes. I currently have
cell K5 refering to the value of a cell in another Worksheet. Is
there a way to automatically rename a sheet based on the formula
result of a cell within that worksheet?

Thank you
Edward


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany


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

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

Thank you both for your advice and quick response.


---
Message posted from http://www.ExcelForum.com/

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

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


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

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

Thank you both for your advice and quick response.


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany


steveB wrote:
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

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

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

Thank you both for your advice and quick response.


---
Message posted from http://www.ExcelForum.com/

Frank,

Thanks again! Keep forgetting about "me". (and I am so narcissistic, too)

--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany


steveB wrote:
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

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

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

Thank you both for your advice and quick response.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

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

Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"nuver " wrote in message
...
Thank you both for all of your help. I am getting closer to getting this
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 get
it to work at all. When you get a chance could you please let me know
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 Sub


---
Message posted from http://www.ExcelForum.com/

Worked like a charm. Thank you all very much! You just saved me a lot o
tedious work.


E

--
Message posted from http://www.ExcelForum.com