"Workbook" change event?

[squidgeny]

Hi guys

Normally I can solve my VBA problems with one google search, but this one has evaded me! So I come to you for help.

I need a piece of code which basically does the following:

"When a value is entered into cell A1 on <i>any sheet</i>, rename the sheet to that value"​

Now, I know how to do this for a given sheet, using a worksheet change event. But in this particular workbook, new sheets are being created all the time, and it's not practical to insert a worksheet change event into the code of each one. So I need a piece of "blanket" code which will apply to the active sheet no matter which sheet is active.

I figured something along the lines of a Workbook_Change event in the workbook object would do the trick... is there such a thing? If not, is there any way to get something like this working?

Thanks!
Squidgeny.

 

[RoryA]

In the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("A1")) Is Nothing Then
        On Error Resume Next
        Sh.Name = Sh.Range("A1").Value
    End If
End Sub

 

[VoG]

Try in the ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Sh.Name = Target.Value
End Sub

 

[squidgeny]

Brilliant, thanks both!

 

[cerfani]

@Rory some comments in your code would be helpful. I personally don't like using solutions if I don't understand them, of course I can just google what each function you call does but it would have been helpful. No clue what "intersect" does but I can infer you are validating the change is the approriate cell.