Is it possible for worksheet name to change whenever a specified cell changes

[kchm_2000]

Hi,

I was wondering whether it would be possible for a worksheet name to change automatically.

normally in order to change a worksheet name we would

worksheets("xxx").select
activesheet.name ="cells(x,y)"

but you have to run the macro before the sheet changes the name to the cells(x,y) if let say you have changed the contents of that cell. How do you make it such that once u change the cell, the sheet name also changes automatically?

[Andy Pope]

You need to use events for this to happen automatically.

If you place this in the Thisworkbook object then it will catch all cell changes on all worksheets.
You can add a test to restrict the cells able to alter the sheet name. In this example it is cell A1 on any sheet.

Please Login or Register  to view this content.

Invalid names will cause the sheet to remain unchanged

[kchm_2000]

oh thanks a lot the code works well but how do I add restrictions I am still fairly new to VBA and need more instructions?

[Andy Pope]

What sort of restrictions? My ability to read minds is nonexistent

[kchm_2000]

sorry I for my careless response, I hope to just restrict it to only one worksheet how can I do that?

[Andy Pope]

Use the sheet object and it's change event.
Right click the sheet tab of the sheet you want to change.
Paste the following.

Please Login or Register  to view this content.

[kchm_2000]

thanks a lot