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/