VBA code - Rename a tab/sheet automatically when updating A1 that contains a formula

[NTschanun]

Hi,
i am working on office 2007 with Windows XP.

i have the following code already available:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'one cell at a time
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Sh.Range("A1")) Is Nothing Then
Exit Sub 'not in A1
End If
On Error Resume Next 'just in case it's not a valid name
Sh.Name = Sh.Range("a1").Value
If Err.Number <> 0 Then
MsgBox Sh.Name & " cannot be renamed to: " & Target.Value
Err.Clear
End If
On Error GoTo 0
End Sub

However this code does not enable me to update the sheetname when the cell A1 is a formula and when the formula in A1 is updated.

I would like to have the sheet name updated as soon as the formula in AD1 is updated.

Hope someone can help.
Regards,
Nicolas

 

[NTschanun]

VoG,
thanks it works. I pasted the code under the workbook code and not under the sheet code.
But when i enable the marco the worksheet and workbook bugs.
Can this be due to the fact that the xls file is save under Microsoft Office Excel 97-2003 Worksheet instead of Microsoft Office Excel 2007 Worksheet ?

Nicolas

 

[VoG]

What error do you get? If you click Debug which line of code is highlighted?

 

[NTschanun]

VoG,
It works now, i pasted the code under the workbook code and not under the sheet code.
However when runing the enabling the macro.. once the sheet names are updated, the workbook bugs. is it because i have links to a sharepoint ? or can this be due to the fact that the work book is saved under an older version of excel then excel 2010 ?

 

[VoG]

What do you mean by "bugs"?

I'm afraid I don't know anything about Sharepoint.

It shouldn't matter that it is saved in XL 97-2003 format.

 

[NTschanun]

VoG,
it is not the code that bugs but the overall excel application ;o((

 

[NTschanun]

bobsan42,
the formula in cell A1 is a link to a different sheet in the same workbook (=+'Fcst Scope'!$T$8).
in the other sheet the formula is refering to another formula index,match,match on the same sheet that extract data from another excel file stored on a sharepoint using the formula index,match,match.
hope this helps

 

[bobsan42]

bobsan42,
the formula in cell A1 is a link to a different sheet in the same workbook (=+'Fcst Scope'!$T$8).
in the other sheet the formula is refering to another formula index,match,match on the same sheet that extract data from another excel file stored on a sharepoint using the formula index,match,match.
hope this helps

wowwww
then better stick to the Calculate event procedure by VoG

 

[NTschanun]

VoG,
Can i use the same code as shared:

Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Range("AD1").Value
On Error GoTo 0
End Sub

in the same workbook but on 3 different sheets ?

If i have the code only on one sheet in the workbook, excel continues to work properly.
If i copy paste the same code in 2 or 3 sheets in the same workbook excel stop responding.

Do we need to amend something to the code ?

Thanks for your input.
Nicolas

 

[NTschanun]

VoG,
Can i use the same code as shared:

Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Range("AD1").Value
On Error GoTo 0
End Sub

in the same workbook but on 3 different sheets ?

If i have the code only on one sheet in the workbook, excel continues to work properly.
If i copy paste the same code in 2 or 3 sheets in the same workbook excel stop responding.

Do we need to amend something to the code ?

Thanks for your input.
Nicolas
<!-- / message -->