Hi,

How can I write this code to place in the workbook of the file, the macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so it
changes instantly when the name is changed and do not need to calculate each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so it
changes instantly when the name is changed and do not need to calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

Reason for repost was because programming was not working a short time ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so it
changes instantly when the name is changed and do not need to calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.

Works perfectly here.


"terilad" wrote in message
...
Reason for repost was because programming was not working a short time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the
macro
changes the sheet tab name as per the individual sheet cell A1 which
is
changed from the first sheet Index of Stock, I am looking to do this
so it
changes instantly when the name is changed and do not need to
calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.

My solution WAS tested.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Reason for repost was because programming was not working a short time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the
macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so
it
changes instantly when the name is changed and do not need to calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.

Do you know what A1 recalculated to that caused the error?

There are lots of things/characters/names that can't be used as a sheet name.

Slashes for dates is a common problem.

terilad wrote:

Reason for repost was because programming was not working a short time ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so it
changes instantly when the name is changed and do not need to calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.


--

Dave Peterson

Do I read this right - you have a list of sheet names on the first sheet
(Index of Stock) which a formula in cell A1 of the target sheet references?

What is the trigger for the sheet name to change? What is the structure of
Index of Stock?


"Project Mangler" wrote in message
...
Works perfectly here.


"terilad" wrote in message
...
Reason for repost was because programming was not working a short time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the
macro
changes the sheet tab name as per the individual sheet cell A1 which
is
changed from the first sheet Index of Stock, I am looking to do this
so it
changes instantly when the name is changed and do not need to
calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.

Yes you are right, sheet 1 is index of stock that contains 2 columns A3:A53
and B3:B53 containing items that are in stock, when I change one of the items
in the columns is changes the name in cell A1 on the individual stock sheet,
what I need to do is for the code to change the sheet name tab also so when I
click on the item in index of stock it finds the sheet tab with that name and
opens that sheet.

Mark

"Project Mangler" wrote:

Do I read this right - you have a list of sheet names on the first sheet
(Index of Stock) which a formula in cell A1 of the target sheet references?

What is the trigger for the sheet name to change? What is the structure of
Index of Stock?


"Project Mangler" wrote in message
...
Works perfectly here.


"terilad" wrote in message
...
Reason for repost was because programming was not working a short time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file, the
macro
changes the sheet tab name as per the individual sheet cell A1 which
is
changed from the first sheet Index of Stock, I am looking to do this
so it
changes instantly when the name is changed and do not need to
calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.





.

Hi Mark,

Not sure if this will do all that you want: It assumes sheetnames in Col A
on the first sheet in the workbook:
A double click on the worksheet name in col A should open the sheet.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Dim TgtName As String
On Error Resume Next
TgtName = Target.Value
Sheets(TgtName).Select
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Errhandler
Application.EnableEvents = False
If Sh.Name < Trim("Index of Stock") Then Exit Sub
If Target.Column < 1 Then
Application.EnableEvents = True
Exit Sub
End If
If Target.Row - 1 Sheets.Count Then
Application.EnableEvents = True
Exit Sub
End If
Sheets(Target.Row - 1).Name = Target
Sheets(Target.Row - 1).Range("A1") = Target
Application.EnableEvents = True
Errhandler:
Application.EnableEvents = True
End Sub


"terilad" wrote in message
...
Yes you are right, sheet 1 is index of stock that contains 2 columns
A3:A53
and B3:B53 containing items that are in stock, when I change one of the
items
in the columns is changes the name in cell A1 on the individual stock
sheet,
what I need to do is for the code to change the sheet name tab also so
when I
click on the item in index of stock it finds the sheet tab with that name
and
opens that sheet.

Mark

"Project Mangler" wrote:

Do I read this right - you have a list of sheet names on the first sheet
(Index of Stock) which a formula in cell A1 of the target sheet
references?

What is the trigger for the sheet name to change? What is the structure
of
Index of Stock?


"Project Mangler" wrote in message
...
Works perfectly here.


"terilad" wrote in message
...
Reason for repost was because programming was not working a short
time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file,
the
macro
changes the sheet tab name as per the individual sheet cell A1
which
is
changed from the first sheet Index of Stock, I am looking to do
this
so it
changes instantly when the name is changed and do not need to
calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.





.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a1")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Yes you are right, sheet 1 is index of stock that contains 2 columns
A3:A53
and B3:B53 containing items that are in stock, when I change one of the
items
in the columns is changes the name in cell A1 on the individual stock
sheet,
what I need to do is for the code to change the sheet name tab also so
when I
click on the item in index of stock it finds the sheet tab with that name
and
opens that sheet.

Mark

"Project Mangler" wrote:

Do I read this right - you have a list of sheet names on the first sheet
(Index of Stock) which a formula in cell A1 of the target sheet
references?

What is the trigger for the sheet name to change? What is the structure
of
Index of Stock?


"Project Mangler" wrote in message
...
Works perfectly here.


"terilad" wrote in message
...
Reason for repost was because programming was not working a short
time
ago.

Mark

"Don Guillett" wrote:

Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"terilad" wrote in message
...
Hi,

How can I write this code to place in the workbook of the file,
the
macro
changes the sheet tab name as per the individual sheet cell A1
which
is
changed from the first sheet Index of Stock, I am looking to do
this
so it
changes instantly when the name is changed and do not need to
calculate
each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value < "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark

.





.