Worksheet Names

D

Dave

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

Thanks

David
 
C

CoRrRan

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

Thanks

David
Click to expand...

Yes, it is possible:

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

HTH,

CoRrRan
 
D

Dave

Yes, it is possible:

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

HTH,

CoRrRan
Click to expand...

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
 
D

Dave

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
Click to expand...

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
 
F

Frank Kabel

[...]
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
Click to expand...

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
 
L

Leo Heuser

Dave said:
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
Click to expand...

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
 
D

Dave

[...]
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
Click to expand...

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
Click to expand...

Thank you very much Frank - I am grateful

David
 
D

Dave

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
Click to expand...

Thanks Leo - much appreciated

David
 
Top