sheet labels

jstritch · Mar 20th, 2002, 05:27 PM

Is there a way to put a formula in a sheet tab? I want to label a sheet with the data in a specific cell.

Jack in the UK · Mar 20th, 2002, 05:40 PM

On 2002-03-20 11:27, jstritch wrote:
Is there a way to put a formula in a sheet tab?

sadley i do not think so .

Fis i use row 1 as dumping ground fpr such detail so never delete row 1 (Chris D)

smozgur · Mar 20th, 2002, 05:51 PM

Of course there is a way

Open the code designer and double click on the sheet object which you want to try naming then paste this code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$1" Then Sheet1.Name = Target
If Err Then Target = ActiveSheet.Name
End Sub

A worksheet's Name property means Label on Worksheets Tab.

Error handling stands for avoid names.

A1 cell used as the label source.

This code will be executed everytime cell A1 changed then if the text value in A1 is allowed to use as the sheet name then sheet name on the tab would be changed.

Regards

Mar 20th, 2002, 05:40 PM	#2
Jack in the UK Join Date: Feb 2002 Posts: 3,063	On 2002-03-20 11:27, jstritch wrote: Is there a way to put a formula in a sheet tab? sadley i do not think so . Fis i use row 1 as dumping ground fpr such detail so never delete row 1 (Chris D) __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 05:27 PM	#1
jstritch Join Date: Mar 2002 Posts: 3	Is there a way to put a formula in a sheet tab? I want to label a sheet with the data in a specific cell.

Mar 20th, 2002, 05:51 PM	#3
smozgur BatCoder Join Date: Mar 2002 Location: Turkey Posts: 764	Of course there is a way Open the code designer and double click on the sheet object which you want to try naming then paste this code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error Resume Next If Target.Address = "$A$1" Then Sheet1.Name = Target If Err Then Target = ActiveSheet.Name End Sub A worksheet's Name property means Label on Worksheets Tab. Error handling stands for avoid names. A1 cell used as the label source. This code will be executed everytime cell A1 changed then if the text value in A1 is allowed to use as the sheet name then sheet name on the tab would be changed. Regards