Excel2003 ... Is it possible to have a cell value linked to a TabSheet Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

Private Sub Worksheet_Activate()
Range("G13").Value = ActiveSheet.Name
End Sub

Put this in worksheet code. After changing the tab name, cell G13 will
reflect the change the next time the sheet is activated.
--
Gary's Student
gsnu200704


"Ken" wrote:

Excel2003 ... Is it possible to have a cell value linked to a TabSheet Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Note: The workbook have to save first


"Ken" wrote:

Excel2003 ... Is it possible to have a cell value linked to a TabSheet Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

Great stuff ... working fine ... :)

Next ... I have a WorkBook with 31 TabSheets

On TabSheet 31 ... Down Col A ... I wish to list the other 30 TabSheetNames
....

These are not my exact parameters & I will need capability to edit code (& I
know nothing about code) ... But bottom line is ... On a separate TabSheet I
would like capability to List down a Col other TabSheets in the Workbook ...

Thanks ... Kha


"Gary''s Student" wrote:

Private Sub Worksheet_Activate()
Range("G13").Value = ActiveSheet.Name
End Sub

Put this in worksheet code. After changing the tab name, cell G13 will
reflect the change the next time the sheet is activated.
--
Gary's Student
gsnu200704


"Ken" wrote:

Excel2003 ... Is it possible to have a cell value linked to a TabSheet Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

Generally, it's a better idea to start a new question in a new thread.
However, one way:

Public Sub ListWorkbooks()
Dim i As Long
With ActiveWorkbook.Sheets
For i = 1 To .Count
ActiveSheet.Cells(i, 1).Value = .Item(i).Name
Next i
End With
End Sub


In article ,
Ken wrote:

Great stuff ... working fine ... :)

Next ... I have a WorkBook with 31 TabSheets

On TabSheet 31 ... Down Col A ... I wish to list the other 30 TabSheetNames
...

These are not my exact parameters & I will need capability to edit code (& I
know nothing about code) ... But bottom line is ... On a separate TabSheet I
would like capability to List down a Col other TabSheets in the Workbook ...

Thanks ... Kha


"Gary''s Student" wrote:

Private Sub Worksheet_Activate()
Range("G13").Value = ActiveSheet.Name
End Sub

Put this in worksheet code. After changing the tab name, cell G13 will
reflect the change the next time the sheet is activated.
--
Gary's Student
gsnu200704


"Ken" wrote:

Excel2003 ... Is it possible to have a cell value linked to a TabSheet
Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name
within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

JE ... my 1st post was looking to Name a Cell from a TabSheet ... Once I
cleared that hurtle my 2nd post was asking how to create a list of TabSheet
Names on a single TabSheet ... I thought it was basically the same thing ...
just single vs multiple ... Sorry ... :(

Above said ... With the Macro you provided:

1: If I want the Range to start the list of TabSheet names @ some place
other than Cell A1 ... How do I edit?

2: If I only want to capture a certain number of TabSheets ... How do I do
this?

3: Where do I locate Macro if I want it to run automatically?

My Thanks for your continued support on these boards ... Kha

"JE McGimpsey" wrote:

Generally, it's a better idea to start a new question in a new thread.
However, one way:

Public Sub ListWorkbooks()
Dim i As Long
With ActiveWorkbook.Sheets
For i = 1 To .Count
ActiveSheet.Cells(i, 1).Value = .Item(i).Name
Next i
End With
End Sub


In article ,
Ken wrote:

Great stuff ... working fine ... :)

Next ... I have a WorkBook with 31 TabSheets

On TabSheet 31 ... Down Col A ... I wish to list the other 30 TabSheetNames
...

These are not my exact parameters & I will need capability to edit code (& I
know nothing about code) ... But bottom line is ... On a separate TabSheet I
would like capability to List down a Col other TabSheets in the Workbook ...

Thanks ... Kha


"Gary''s Student" wrote:

Private Sub Worksheet_Activate()
Range("G13").Value = ActiveSheet.Name
End Sub

Put this in worksheet code. After changing the tab name, cell G13 will
reflect the change the next time the sheet is activated.
--
Gary's Student
gsnu200704


"Ken" wrote:

Excel2003 ... Is it possible to have a cell value linked to a TabSheet
Name?

My TabSheets are peoples names ... & in a cell within the TabSheet I have
the same name ... If I change TabSheet name ... I would like the name
within
the TabSheet cell to change as well ... Can I do this?

Thanks ... Kha

In article ,
Ken wrote:

1: If I want the Range to start the list of TabSheet names @ some place
other than Cell A1 ... How do I edit?

One way:

Change

ActiveSheet.Cells(i, 1).Value

to

ActiveSheet.Cells(x + i, y).Value

where x and y reflect your desired starting point (e.g., if J12, then
x=11, y = 10).


2: If I only want to capture a certain number of TabSheets ... How do I do
this?

Instead of

For i = 1 To .Count

use

For i = 1 to CertainNumber

3: Where do I locate Macro if I want it to run automatically?

When do you want it to automatically run?

JE ... A shortcoming on my part is that I know nothing about Code ... I
record Macro's only ... then attempt to get creative with a few edits &
cut/paste ... So I will have to see if I can work with what you have provided
me ... I think J = 10 ... I am still working on the rest ... :)

Again ... my Thanks for supporting these boards ... Kha

"JE McGimpsey" wrote:

In article ,
Ken wrote:

1: If I want the Range to start the list of TabSheet names @ some place
other than Cell A1 ... How do I edit?

One way:

Change

ActiveSheet.Cells(i, 1).Value

to

ActiveSheet.Cells(x + i, y).Value

where x and y reflect your desired starting point (e.g., if J12, then
x=11, y = 10).


2: If I only want to capture a certain number of TabSheets ... How do I do
this?

Instead of

For i = 1 To .Count

use

For i = 1 to CertainNumber

3: Where do I locate Macro if I want it to run automatically?

When do you want it to automatically run?