Accessing the data in an array in a different sub

[rockhyrax123]

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.

Please Login or Register  to view this content.

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.

[Leith Ross]

Hello rockhyrax123,

Welcome to the forum!

Please Login or Register  to view this content.

[rockhyrax123]

Thank you, that was helpful.

I am now having other issues though.

I am getting a type mismatch between ActiveSheet.Name and WSArray(ActiveSheet.Index). ActiveSheet.Name seems to be a variant but I can't work out what type WSArray(ActiveSheet.Index) is. CVAr(WSArray(ActiveSheet.Index) doesn't work either.