Hi,
I am very good at excel until it comes to using VBA. I just don't get it.
I am trying to stop anybody changing the names of tabs if the tabs are protected. I could relatively easy stop the renaming of them altogether with a private sub for each sheet saying if the sheet name <> 'whatever it is currently called', it gets set back to 'whatever it is currently called'. However, I want them to be able to change them if they are unprotected (each sheet will at times be protected and at other times not). I don't want the name being set back to 'whatever it is currently called' if it has undergone two or three legitimate name changes since I wrote the code.
With that in mind, I wrote a macro, which I think works, to store, before save, all the names of the tabs in an array.
What I want to do is to have a workbook macro, on sheetselectionchange, that compares the name of the active sheet with the corresponding entry in WSArray. I think I can use ActiveSheet.Name on the one side and ActiveSheet.Index as the entry number in the array. I just can't work out how to get the array into another sub. I have looked at examples on the web but they don't actually explain step by step how the thing works. I would be very grateful for any help.
Thank you.
Bookmarks