vba - Cannot seem to move worksheet's content reliably

Question

This is already part 4 of what has now become my 1st StackOverflow trilogy :) Hopefully bringing it all together will lead to an answer, or prove it cannot be handled.

Quick summary

(See the background for further details and conclusions.)

I have an Excel workbook with a digitally signed VBA project, to be used by my customer. One of the functions it has is that the user can make copies of sheets (the templates), and my code can recognize the template sheets and the copies themselves (for further processing). The sheets themselves contain sheet-scoped named ranges, and sheets use each others names all over the place in formulas. They also all use tables (the ListObject one). Furthermore, my intention is for the user to be free to add whatever they want to these sheets as well.

My first set-up found these sheets by code name, but since using the code name implies the sheets having a code module (even though they do not have any code behind them), copying these sheets on their computer (without my digital certificate on it) will break the signature of the VBA project. My customer will from then on get macro security warnings after he has saved and re-opened the workbook. Unwanted.

Fix 1 was to adjust my code to identify these sheets by using a code in a special name on the sheets. This way I do not rely on any VBA details of the sheet. And since sheets can have a missing VBA module (again see the background - basically create a new sheet and do NOT open the VBA IDE from then on), my hope was on finding a way to scrub the VBA off of these sheets. I'd use it when making a delivery of the solution now, and also when upgrading the code in live production versions of the workbook in the future. All automated to reduce the chance of errors.

The problem then is how to move the existing Excel sheet content? I see two options:

1. Move the content of the sheets to new sheets
2. Move the sheets into another (VBA-free .xlsx) workbook and back

Moving content to new sheets

I already started a separate post on this. It's still on-going, but I highly suspect it's a dead end. I used the following pseudo code:

sheetName = sourceSheet.Name
Set destSheet = ThisWorkbook.Worksheets.Add
Call sourceSheet.Cells.Cut(destSheet.Cells)
Call sourceSheet.Delete
destSheet.Name = sheetName
Call fixBrokenFormulasAndOtherStuff(ThisWorkbook)

Summary: moving the content of a sheet to a new sheet makes a mess of the sheet-scoped names; after the move you're left with forked name definitions, both branches being in use, and not an easy way to implement fixBrokenFormulasAndOtherStuff above.

Moving content to new workbook

This option I tried to solve with my other question here. That post also doesn't seem to go anywhere however...

For this I used the following pseudo code:

Set scrubWorkbook = Workbooks.Add(1)
Set allSheets = CollectSpecialSheets(ThisWorkbook)
Call MoveSheets(allSheets, ThisWorkbook, scrubWorkbook)
Call SaveAndReopenScrubWorkbook(scrubWorkbook)
Set allSheets = CollectSpecialSheets(scrubWorkbook)
Call MoveSheets(allSheets, scrubWorkbook, ThisWorkbook)

In the implementation of MoveSheets, the tables are sorely in the way. While Excel can move multiple sheets in one go, preserving their links, it refuses to do so when there's any table on the sheet. Therefore Worksheets(Array(<all names>)).Move doesn't cut it.

Moving sheets individually works (via Worksheet.Move), but then we again get the same range name mangling we had when moving the content of the sheet itself. So we're then again left with an unwanted obligatory Call fixBrokenFormulasAndOtherStuff.

Moving thus doesn't work, but we can also copy, right? However, copying sheets has the same table-restriction as moving, so here we'd also have to do it sheet by sheet. Which in turn means that all range name using formulas on the copies of the sheets all still point back to the original workbook sheets, which need to be deleted, which in turn kills these formulas. In other words: another need for a Call fixBrokenFormulasAndOtherStuff...

Questions

Now I cannot believe I'm the first person/company to both sign his VBA project, and also have his users be able to copy their sheets?! How do others solve this dilemma? Or do they just not sign VBA projects, and direct the users to reducing their macro security settings?

The reason I'm not too keen on writing a fixBrokenFormulasAndOtherStuff function is that I really cannot fathom what I'd need to put in it. Of course I could loop over all cells on all sheets and fix any formulas there, but then there are lots of other places formulas get used. Data validation lists, form control linked values, ...? Now I'm a well-versed Excel specialist, but I do not dare to claim I know of every little last Excel detail; every now and then users always keep surprising me with stuff I didn't even know was in Excel :) Or maybe I just have more creative users than average... :P

Answer 1

Waitting for answers