"Workbook" change event?

squidgeny

Board Regular
Joined
Jul 26, 2011
Messages
130
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
In the ThisWorkbook module:
Code:
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
 
Upvote 0
Try in the ThisWorkbook module

Code:
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top