Worksheet Names
 

Hi, can anyone tell me if it is possible to change a worksheet name
based on the value of a cell

Thanks

David

Worksheet Names
 

Hi David

With Worksheets("Sheet1")
.Name = .Range("A1").Value
End With

will do the job

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave" skrev i en meddelelse
...
Hi, can anyone tell me if it is possible to change a worksheet name
based on the value of a cell

Thanks

David

Worksheet Names
 

On 06 Aug 2004 09:39:29 GMT, CoRrRan
wrote:

Dave wrote in
:

Hi, can anyone tell me if it is possible to change a worksheet name
based on the value of a cell

Thanks

David


Yes, it is possible:

ActiveSheet.Name = Range("A1").Value

HTH,

CoRrRan

Thanks guys for the prompt reply - I should have said that I am new to
VB programming - can you tell me exactly where and how I should enter
this code

Many thanks

David

Worksheet Names
 

On Fri, 06 Aug 2004 11:49:41 +0100, Dave wrote:

On 06 Aug 2004 09:39:29 GMT, CoRrRan
wrote:

Dave wrote in
m:

Hi, can anyone tell me if it is possible to change a worksheet name
based on the value of a cell

Thanks

David


Yes, it is possible:

ActiveSheet.Name = Range("A1").Value

HTH,

CoRrRan

Thanks guys for the prompt reply - I should have said that I am new to
VB programming - can you tell me exactly where and how I should enter
this code

Many thanks

David

Bit more info might help I think. I have 10 worksheets all identified
with dates ie sheet1 is now 2 Aug 04 sheet 2 09 Aug 04 etc.

At the moment cell B3 is 02 Aug 04 but if I was to change that to 06
Spe 04 I would like sheet1 to be showing the same date and all the
other sheets in the workbook.

The date filed in each of the worksheets which I would like to tag to
the sheetname are in cell B3.

If this is possible can I use a macro to run it automatically when
cell B3 changes?

thanks again

David

Worksheet Names
 

[...]

Bit more info might help I think. I have 10 worksheets
all identified
with dates ie sheet1 is now 2 Aug 04 sheet 2 09 Aug 04
etc.

At the moment cell B3 is 02 Aug 04 but if I was to change
that to 06
Spe 04 I would like sheet1 to be showing the same date
and all the
other sheets in the workbook.

The date filed in each of the worksheets which I would
like to tag to
the sheetname are in cell B3.

If this is possible can I use a macro to run it
automatically when
cell B3 changes?

thanks again

Hi david
you can put this kind of code in your worksheet_change
event (of your worksheet module). e.g. try the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit
Sub
On Error GoTo errhandler
Application.EnableEvents = False
Me.Name = Target.Value
errhandler:
Application.EnableEvents = True

End Sub

Worksheet Names
 

"Dave" skrev i en meddelelse
...

Thanks guys for the prompt reply - I should have said that I am new to
VB programming - can you tell me exactly where and how I should enter
this code

Many thanks


You're welcome, David.

Here's how to do it:


1. Rightclick a sheet tab and choose "View code" (or similar)
2. In the project window (left upper screen) find your project
3. Doubleclick "ThisWorkbook" for your project.
4. Copy the code below and paste it to the right hand window.
5. Return to the sheet with <Alt<F11 and save the workbook.

By using "Target.Text" instead of "Target.Value", you're sure to
get the *formatted* content of the cell, which might be important,
when it comes to dates or times.
On Error Resume Next makes the code continue, if the cell
is blanked. Otherwise an error would occur, since a blank cell
has no text.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Leo Heuser, 6 Aug. 2004
Dim CheckCell As String
CheckCell = "B3"

On Error Resume Next

If Not Intersect(Sh.Range(CheckCell), Target) Is Nothing Then
Sh.Name = Target.Text
End If

On Error GoTo 0
End Sub

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Worksheet Names
 

On Fri, 6 Aug 2004 04:05:25 -0700, "Frank Kabel"
wrote:

[...]

Bit more info might help I think. I have 10 worksheets
all identified
with dates ie sheet1 is now 2 Aug 04 sheet 2 09 Aug 04
etc.

At the moment cell B3 is 02 Aug 04 but if I was to change
that to 06
Spe 04 I would like sheet1 to be showing the same date
and all the
other sheets in the workbook.

The date filed in each of the worksheets which I would
like to tag to
the sheetname are in cell B3.

If this is possible can I use a macro to run it
automatically when
cell B3 changes?

thanks again

Hi david
you can put this kind of code in your worksheet_change
event (of your worksheet module). e.g. try the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit
Sub
On Error GoTo errhandler
Application.EnableEvents = False
Me.Name = Target.Value
errhandler:
Application.EnableEvents = True

End Sub

Thank you very much Frank - I am grateful

David

Worksheet Names
 

On Fri, 6 Aug 2004 13:38:28 +0200, "Leo Heuser"
wrote:

"Dave" skrev i en meddelelse
.. .

Thanks guys for the prompt reply - I should have said that I am new to
VB programming - can you tell me exactly where and how I should enter
this code

Many thanks


You're welcome, David.

Here's how to do it:


1. Rightclick a sheet tab and choose "View code" (or similar)
2. In the project window (left upper screen) find your project
3. Doubleclick "ThisWorkbook" for your project.
4. Copy the code below and paste it to the right hand window.
5. Return to the sheet with <Alt<F11 and save the workbook.

By using "Target.Text" instead of "Target.Value", you're sure to
get the *formatted* content of the cell, which might be important,
when it comes to dates or times.
On Error Resume Next makes the code continue, if the cell
is blanked. Otherwise an error would occur, since a blank cell
has no text.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Leo Heuser, 6 Aug. 2004
Dim CheckCell As String
CheckCell = "B3"

On Error Resume Next

If Not Intersect(Sh.Range(CheckCell), Target) Is Nothing Then
Sh.Name = Target.Text
End If

On Error GoTo 0
End Sub

Thanks Leo - much appreciated

David

Worksheet Names
 

Thanks Leo - much appreciated

You´re welcome, David.