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

 

[GTO]

"when the cell A1 is a formula and when the formula in A1 is updated."

If you are saying that you want it to run when the results of a formula change, try the Worksheet_Calculate event.

 

[bobsan42]

What is the Formula? Does it refer to other cells or what?

 

[NTschanun]

the formula used in Cell A1 is =+A3&" for region"

 

[NTschanun]

when the formula in A1 is updated

 

[NTschanun]

GTO, i am not an expert in VBA, the code mentionned in my initial message is coming from internet. When you say : " try the Worksheet_Calculate event", i presume you mean more then just updating the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

to

Private Sub Workbook_Sheet_Calculate(ByVal Sh As Object, ByVal Target As Range)?
If you could share the full code that would be great.
Thanks
Nicolas

 

[VoG]

Try

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

 

[NTschanun]

Vog,
thanks, but it does not work ;o(
Nicolas

 

[bobsan42]

the formula used in Cell A1 is =+A3&" for region"

if cell A3 is not a formula then:
this means that instead of looking for changes in A1 look for changes in A3
so replace in your code A1 with A3 and if A3 is manually changed it should work.

if A3 is a formula again: what is it ...
and so on ....

 

[VoG]

Where did you put the code? You need to right click the sheet tab, select View Code and paste in the code.