Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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, .... |
#9
|
|||
|
|||
:-)
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, .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make some characters in a cell bold and some not? | Excel Discussion (Misc queries) | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) | |||
Define inputs and outputs to make functions from sheets | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
how do I make a cell flash | Excel Discussion (Misc queries) |