Retaining VBA Protection When Sheet is Exported

[mwcalc]

Hello all,

Normally I see people posting to find out how they can remove VB protection, I'm looking for the opposite. I have a workbook with multiple sheets, the work has VBA set to "Lock project to viewing", the sheets themselves are also protected to help stop accidental deletions. I have a macro that exports the final totals worksheet (only 1 worksheet, with all the macros for that sheet contained in the sheet module) to a new workbook. The sheet protection is remaining, but the VB Editor is no longer locked for viewing. How can I export the sheet & keep the VBE locked?

Thanks,
Michael

 

[mrmmickle1]

Here's some documentation that may provide some insight. I have rarely had to use the VBA object model...

Lock/Unlock VBA Project programmatically in Excel VBA Handy Code Forum

Don't forget you have to enable it....



File >Options >Trust Center > Trust Center Settings > Macro Settings > Check Trust Access to the VBA Project Object Model

 

[mwcalc]

Matt -

Thanks, although I'd like to use a non-SendKeys option if there is one.

 

[mrmmickle1]

Like I said. I'm not too familiar with the object model but, I did find a post from an experienced user on OzGrid that says:

Unfortunately, VBA does not expose the VBProject passwords in its object model. Consequently, the only way to achieve what you want is via a workaround.

Unprotecting VBA Project via VBA code to change code


SEE POST 4

Another workaround might be copying the entire workbook and then deleting the additional sheets that are no longer needed.