Workbook Macro

[terilad]

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

 

[Don Guillett]

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

 

[terilad]

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

Mark

 

[Project Mangler]

Works perfectly here.

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

Mark

 

[Don Guillett]

My solution WAS tested.

 

[Dave Peterson]

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.

 

[Project Mangler]

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?

 

[terilad]

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]

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

 

[Don Guillett]

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