Workbook Macro


T

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
 
Ad

Advertisements

T

terilad

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

Mark
 
D

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.
 
Ad

Advertisements

P

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?
 
T

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
 
P

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
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top