i need to make the sheets name come from a cell
 

I am wondering if its possible to name a sheets in excel, with data held in a
cell in the same worksheet
thanks for the help
regards sam

Hi
do you need this automatically?. If yes try the following code in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$A$1" then
if target.value<"" then
application.enableevents=false
me.name=target.value
application.enableevents=true
end if
end if
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"sam wildig" <sam schrieb im
Newsbeitrag ...
I am wondering if its possible to name a sheets in excel, with data
held in a
cell in the same worksheet
thanks for the help
regards sam

Here is one I posted on another group. Just change to a worksheet_change
event, if desired

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address < Cells(1, "a").Address Then Exit Sub
On Error Resume Next
ActiveSheet.Name = Target
End Sub


--
Don Guillett
SalesAid Software

"sam wildig" <sam wrote in message
...
I am wondering if its possible to name a sheets in excel, with data held
in a
cell in the same worksheet
thanks for the help
regards sam

Frank -

Just out of curiosity, why the application.enableevents lines? Are any
events fired by a change in the sheet name?

FWIW, I've got a similar routine that also (minimally) traps invalid
sheet names at

Changing Sheet Name to match cell, automatically
http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Frank Kabel" wrote:

Hi
do you need this automatically?. If yes try the following code in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$A$1" then
if target.value<"" then
application.enableevents=false
me.name=target.value
application.enableevents=true
end if
end if
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"sam wildig" <sam schrieb im
Newsbeitrag ...
I am wondering if its possible to name a sheets in excel, with data
held in a
cell in the same worksheet
thanks for the help
regards sam

Hi JE
AFAIK the calculate event gets fired if a formula is referencing this
changed sheet

--
Regards
Frank Kabel
Frankfurt, Germany

"JE McGimpsey" schrieb im Newsbeitrag
...
Frank -

Just out of curiosity, why the application.enableevents lines? Are
any
events fired by a change in the sheet name?

FWIW, I've got a similar routine that also (minimally) traps invalid
sheet names at

Changing Sheet Name to match cell, automatically
http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Frank Kabel" wrote:

Hi
do you need this automatically?. If yes try the following code in
your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$A$1" then
if target.value<"" then
application.enableevents=false
me.name=target.value
application.enableevents=true
end if
end if
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"sam wildig" <sam schrieb im
Newsbeitrag
...
I am wondering if its possible to name a sheets in excel, with
data
held in a
cell in the same worksheet
thanks for the help
regards sam

Ah, I wasn't thinking of that. I'd assumed that one would *want* to fire
the _Calculate() event if cells were calculating based on a cell
reference, since the Calculate event code could be independent of the
_Change() macro. Especially since the _Calculate() macro runs before the
_Change() macro does, potentially acting on invalid data (e.g., if an
INDIRECT() references N5, the first _Calculate macro will operate while
that formula evaluates to #REF! if the sheet doesn't exist, or the wrong
sheet if a sheet with that name already exists).

Using the Application.EnableEvents = False makes sense, of course, if
you know that you don't want to run the _Calculate() macro,.

In article ,
"Frank Kabel" wrote:

AFAIK the calculate event gets fired if a formula is referencing this
changed sheet

Hi JE
agree with you that it could also be a good idea to let the calculate
event happen. I just tend to disable events in event procedures to
prevent multiple calls. e.g. the calculate event changes a cell which
changes the sheetname again, ....




"JE McGimpsey" schrieb im Newsbeitrag
...
Ah, I wasn't thinking of that. I'd assumed that one would *want* to
fire
the _Calculate() event if cells were calculating based on a cell
reference, since the Calculate event code could be independent of the
_Change() macro. Especially since the _Calculate() macro runs before
the
_Change() macro does, potentially acting on invalid data (e.g., if an
INDIRECT() references N5, the first _Calculate macro will operate
while
that formula evaluates to #REF! if the sheet doesn't exist, or the
wrong
sheet if a sheet with that name already exists).

Using the Application.EnableEvents = False makes sense, of course,
if
you know that you don't want to run the _Calculate() macro,.

In article ,
"Frank Kabel" wrote:

AFAIK the calculate event gets fired if a formula is referencing
this
changed sheet

Yup - have been down that circular road a time or two...

In article ,
"Frank Kabel" wrote:

agree with you that it could also be a good idea to let the calculate
event happen. I just tend to disable events in event procedures to
prevent multiple calls. e.g. the calculate event changes a cell which
changes the sheetname again, ....

:-)
but a neat thing you could do to a co-worker on April 1st....

--
Regards
Frank Kabel
Frankfurt, Germany

"JE McGimpsey" schrieb im Newsbeitrag
...
Yup - have been down that circular road a time or two...

In article ,
"Frank Kabel" wrote:

agree with you that it could also be a good idea to let the
calculate
event happen. I just tend to disable events in event procedures to
prevent multiple calls. e.g. the calculate event changes a cell
which
changes the sheetname again, ....