Help with excel template VBA

[jarzack]

I'm currently working on a new Excel form.

The first group will use this excel template, unprotect the workbook, delete certain rows, then add data. Then they will protect the workbook and Save as a new filename (example 123456DIR).

The second group will leave the file protected and only enter data in certain unlocked cells. If unlocked cell is blank don't allow save. If ALL unlocked cells contain a value allow file save. When file is saved the filename should be cells (E4) & (J9).

Any help would be great!

 

[PeteWright]

Hi @jarzack
As for the first group, it's only users opening, unprotecting, editing, protecting and closing a workbook, right? Nothing more. That should be fine.

The second part will be tricky. There are a few things to consider:

1. How will the Workbook (VBA) "know" if a file was already modified by group one?
   There should be some information somewhere in the workbook, but Excel cannot store a variable beyond the lifespan of an opened workbook. After it's closed the variable is gone for good.
   So I was thinking and researching and thinking again and I got an idea: What about a protected cell somewhere at the beginning or end of the workbook? Like A1. Where the user from group one enters a keyword like "ready" indicating that the workbook is edited and ready for group two.
2. Disabling saving of the workbook can be partially done, but any workaround has its downside and an experienced user will know how to save the workbook anyway.
   Method #1 would be a code firing at the "Save" or "Save As" event which then checks for empty cells and stops if true. The problem (which I figured out by testing) is that on clicking the [x] button or pressing the [Esc] key a new dialog box pops up offering to save the workbook.
   The other methods mess with the code making it unpredictable what happens next.

Could you provide all requirements and do you agree with the workaround of an extra "Edit Status" cell?

Let me know.