How to save a workbook in VBA without protecting it's structure?

[Ian Garioch]

Hi All,

Is there any way to save without protecting structure?

This code isn't working...

Please Login or Register  to view this content.

I've tried preceding it and following it with ActiveWorkbook.Protect but that doesn't seem to help

I'm a newbie to VBA so probably have syntax wrong

Please Login or Register  to view this content.

Any help greatly appreciated

Ian Garioch

[Medpack]

Hi Ian,

To answer your question, yes.

However, I don't understand why;

Please Login or Register  to view this content.

isn't working for you? Do you understand what structure protects? It only keeps you from moving sheets around.

What exactly do you want accomplished?

[Ian Garioch]

The workbook is a proposal template where users edit one of several sheets to create a proposal for an enquiring customer. Because the template includes both costs and prices, my mostly borrowed VBA code creates duplicates of the active sheet, edits costs out of the duplicate sheets and then copies the duplicates to new workbooks which are issued to the enquiring customer. The VBA code then saves the template workbook which is when the structure becomes locked. Users regularly need to return to the workbook to create a revision of the sheet they originally edited but find they cannot duplicate it using the copy function (acessed from the mouse right click menu)

[Medpack]

Can you post your code for this procedure and/or a sample workbook? Sounds like you just need to assign Structure:=False at the end of your code as outlined above.

Or, you can just set the property of your template workbook to readonly and force the user on the workbook_open event to saveas the file on their desktop, thus preserving your template file on your network for future edits.

I'm also a bit confused in the first place why you let your template workbook get edited by any user?

[Ian Garioch]

Sincere Apologies Medpack,
Users create a new "Proposal Template1.xltm" from "Proposal Template.xltm" each time they begin a new quotation. (New, Recent Templates, Select the template "Proposal Template", Create).
Users then immediately remane "Proposal Template1.xltm"when they Save As "Enquiry Reference.xlsm".
There's probably a better way to this.

While you replied, I manged to get your code to work - I'm not sure why it differred from my (borrowed) code, but it did the trick. Thank you

- Now I'm on another headache which I'll post as a separate thread.