Copy worksheet from one WB to another, overwrite existing worksheet with same CodeName

[Renns]

Hi, I'm looking for some help with the following:

The goal:
I've got multiple identical files ("UserBook") which are used by separate users. One specific copy of the same file ("MasterBook") is located on a shared drive. Each time a specific worksheet within MasterBook is updated, I need all the UserBook files to reflect the changes.

My attempt:
I've got it pretty much figured out. In each of the user files, upon Workbook_Open() I have it compare the date it was last updated (which it gets from a cell value on the worksheet) with the modification date of the master file. If the master file has been modified since the last time the user file was updated, then delete the specific worksheet on the userfile and replace it with the same worksheet from the MasterBook.

The problem:
Even though the UserBook worksheet is deleted BEFORE the MasterBook worksheet is copied over, the worksheet's CodeName is still changed from "wsTheList" to "wsTheList1". This is unacceptabe due to the CodeName being heavily used throughout the rest of the program.

My Code:

Please Login or Register  to view this content.

I tried changing the CodeName before deleting the worksheet:

Please Login or Register  to view this content.

This worked great and I thought I was done... but once the VBProject is password protected, this no longer works.

Any help on this would be much appreciated. I feel like I'm missing something simple...

[Richard Buttrey]

Hi

As I understand it the VBA object does not include any references to VBA passwords so I don't believe you can do what you want.

Just as a matter of interest is it really vital to protect the VBE in what I'm assuming is an internal workbook.

[xladept]

Hi Renns,

Maybe you could rename it before you delete:

Please Login or Register  to view this content.

[Artik_PL]

I feel like I'm missing something simple...

On the contrary, it will be hard. Officially, there is no way to deprotection of the project. Unofficial ways are uncertain. You may have to resign from references to sheets by CodeName.
But...
And if you need to replace the sheets? Can not you copy the data from UsedRange Master to UserSheet?

Artik

I apologize for my language. I use a translator.

[Renns]

Richard Buttrey:
True, I am unable to unprotect the VBProject during runtime. Unfortunately, the protection is required. It's reaching 5K lines of code and will be used by many people. So I am hoping someone knows an alternate way of acheiving my goal.

xladept:
Thanks, but the worksheet Name is not the problem, it's the CodeName that is giving me trouble. You see, even on a protected worksheet any user can change the Name by double-clicking on the tab name. This is why all my code refers to worksheets by their CodeNames.

Artik_PL:
You're right, there's no safe way to unprotect the VBProject during runtime, and I'm not interested in playing with the SendKeys method. As for just copying the data from one sheet to the other... that's something I may have to resort to, although it doesn't offer as complete of a solution. The worksheet in question has a table of ~900x3 with cell comments in each, a hidden table of ~900x17 used for filter criteria, another hidden table of ~30x3, as well as 16 checkboxes, 6 commandbuttions, 1 textbox and approx 300 lines of code. If any of the controls or the code are edited, then copying and pasting won't be enough.

-

To go back to my original post... the problem I'm having is not "How do I unprotect my VBProject during runtime?", but rather "Why does deleting the worksheet not free up its CodeName for use by the replacement worksheet?"

Thanks for the fast responses, by the way.

[Richard Buttrey]

Hi,

Can you upload the master and one user workbook. I'm finding it hard to understand the various sheet names and code names.

[Renns]

Hi,

Can you upload the master and one user workbook. I'm finding it hard to understand the various sheet names and code names.

Here is a mock version of my project to demonstrate this feature. Just place both files in the same location, then open UserList.xlsm.

[Richard Buttrey]

To go back to my original post... the problem I'm having is not "How do I unprotect my VBProject during runtime?", but rather "Why does deleting the worksheet not free up its CodeName for use by the replacement worksheet?"

Hi,

The essence of the problem is that although VBA deletes the sheet in the sense that all the sheet properties are blank, it doesn't drop the sheet object until the macro finishes. I've played around with using an On Time macro to see if it releases the sheet object but wasn't surprised to find that it doesn't. Clearly it would seem the macro needs to halt first.

The only thing I can suggest at the moment therefore is to split the macro into two. End the first one with the wsList.Delete instruction, and then get the user to click another button which would start the second macro which contains the rest of the code. Not ideal I know but at the moment I can't think of any other way given that you also want to lock the VBE with a password.

Incidentally you mentioned that you always used the sheet code name - which of course is eminently sensible given Sods law says someone will change a sheet name, but there is a line

Worksheets("The List").Activate

which doesn't.

If I can think of any other solution I'll come back.

[Renns]

Thanks Richard.
Yeah, I changed that line to Worksheets("The List").Activate just for the sake of this example so it would run. In the actual project all worksheet references use CodeNames.

I have also been toying around with it, and have come to the same conclusion; it must be split into two separate macros. So I did something like this:

Please Login or Register  to view this content.

This way does work, but it leaves a step in the hands of the user. If they decide not to obey the message (or don't read it, as many don't), and if try using any of the features or running any of the macros which use this worksheet while it's deleted, everything will crash. I don't want any crashes in a polished product, so I'm going to have to bang my head against the wall a little longer before accepting this.

I tried throwing in a UserBook.Close True at the end, so at least they couldn't use the file if the worksheet is missing, and when they open it again it will complete the update... but apparently that still behaves as though the macro was never stopped.

In fact, that does something even weirder... the wsTheList1 worksheet appears again (bad), but this time there is also a ghost workbook with the CodeName wsTheList. It's right under ThisWorkbook in the Project Explorer and it has a workbook icon instead of a worksheet icon. It won't let me view it or do anything with it, not even remove it. Continuing to update the file in this way creates additional worksheets CodeNamed wsTheList2, wsTheList3, etc.

I've attached the new working files. Try it "as is" to see it work (with manual restarting)... then remove the ' in front of UserBook.Close and try it again to see what I mean.

[Renns]

New Plan...
I'll leave it the way it works, where it's up to the user to close the file and open it again.

However...
I'll throw in a few lines of code at the beginning of all macros which require that worksheet:

Please Login or Register  to view this content.

That should do it.

[Artik_PL]

Renns.
When you delete a sheet, vbProject must be recompiled. This will happen when the procedure UpdateTheList is completed. You need to run a new procedure (CopySheet) by OnTime to complete the copy sheet with the old name.
Attention! Recompile destroys all variables! If you are using global variables, you have to rebuild them before or after copying sheet.

Please Login or Register  to view this content.

I apologize for my language. I use a translator.

Artik

[Richard Buttrey]

Thanks Richard.
Yeah, I changed that line to Worksheets("The List").Activate just for the sake of this example so it would run. In the actual project all worksheet references use CodeNames.

I have also been toying around with it, and have come to the same conclusion; it must be split into two separate macros. So I did something like this:

Please Login or Register  to view this content.

This way does work, but it leaves a step in the hands of the user. If they decide not to obey the message (or don't read it, as many don't), and if try using any of the features or running any of the macros which use this worksheet while it's deleted, everything will crash. I don't want any crashes in a polished product, so I'm going to have to bang my head against the wall a little longer before accepting this.

Hi,

Actually during my testing all that was needed was to stop the macro and then restart another one. There is no need to close the workbook as you seem to be suggesting in your Else statement.

So to clarify, my idea was

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

followed by

Please Login or Register  to view this content.

and have a button or something that the user needs to click to start the 'AnotherMacro'

[Renns]

Artik:
Brilliant! That works perfectly and produces exactly the results I was looking for! Thank you so much.

Richard:
Yeah, I added the "close and re-open the file" bit, hoping to use UserFile.Close in order to prevent users from having access to the file while wsTheList was missing. Thanks for all your help, your method also works well. I'm going to use the .OnTime method though, as it allows the complete update to occur without interruption and with minimal user involvement.

Cheers!

[Richard Buttrey]

@Artik,

That's extremely interesting.

I thought I'd played around with the OnTime method during testing but obviously not rigorously enough, and not in quite the simple way you came up with. Well done.

There's a rep point on the way.

[Artik_PL]

Richard Buttrey,
For a long time I thought that it is impossible to pass parameters to a procedure with OnTime. A few years ago I found a solution, that I showed. You can pass parameters String and Numeric, but did not Object.

Well done.

Thx.

Artik

[Renns]

You can pass parameters String and Numeric, but not Object.

I was wondering why you passed the workbook.names as strings instead of just workbooks, so I played with it and sure enough. Just Strings and Numeric, eh? Good to know. Thanks again.

[Renns]

Attention! Recompile destroys all variables! If you are using global variables, you have to rebuild them before or after copying sheet.

Uh oh. I though I was in the clear because any Const variables still worked... BUT... after recompiling, things like "Worksheet_SelectionChange" no longer work. For example, the following code made comments appear whenever a cell with a comment was selected:

Please Login or Register  to view this content.

But after the recompile it no longer works. In fact, the comments don't appear at all until after I close and then re-open the file. Is there a way to make it work without restarting Excel?

[Artik_PL]

At the moment I do not have a brilliant idea. The only thing comes to my head is to save the file and re-open the workbook with OnTime. Not restart Excel!
I still think.

I apologize for my language. I use a translator.

Artik

[Artik_PL]

In my Excel (2K10) works fine after copying sheet. Without re-opening.

..::Edit:
I checked it on the XL2007. It works fine.::..


Artik

[Renns]

Ok, apparently copying the worksheet now removes ALL cell comments. I don't understand this. The worksheet in MasterBook has all comments, but when the ws is copied over to UserBook, the comments are all gone. They're not hidden, they just don't exist. WHy would copying the worksheet not copy the comments on that worksheet?

I am only having this problem in my large project. The small sample files DO work properly, including copying the comments over. But when the exact method is applied to the larger project, the comments do not get copied over. Furthermore, I get the same problem when I manually copy one cell that has a comment in it from the master workbook and paste it into a brand new workbook. The cell value and format are copied, but the comment is not copied. It worked before, but now it doesn't. WTF?

[Richard Buttrey]

Hi

Does the failure of comments to copy across also happen when you manually copy a sheet to a new workbook, i.e. right clicking the sheet name and choosing Move or Copy, Create a Copy and select 'New Book'?

How many comments are there.
MySheet.Comments.Count

[Renns]

Ok, so apparently I am an idiot.
The worksheet is protected, therefore I had to unprotect it, then copy it over, then protect it again.

I had that in my previous code, but when I applied the OnTime method from the sample files I forgot to add that one line. Blargh.

I need some fresh air...

[Edit]And damn you for being so fast and helpful, Richard! I was just going to delete my last post to hide my stupidity, but you already saw it. lol.[/edit]

[Richard Buttrey]

Been there: got the T-shirt