Excel Spreadsheets, and thus PostTrans templates, can be protected from users editing the spreads sheet.   Many methods can help protect the PostTrans template:

• Hiding Sections of the template
• Putting the Header fields in hidden section of template
• Grouping sections, to temporally hide sections
• Make template file read only
• Save the template as a Excel Template – User cannot change the template
• Disable the PostTrans Setup button
• Protect sections of the sheet, to stop users editing certain areas

Please note that the demo/training videos say a Tag must exist in Cell A1, for transaction type imports.  Later in development PostTrans was altered to look at the tag value in the comment of cell A1.  Thus the tag text in cell A1 can be deleted.

How to hide a section of the template

PostTrans uses the tag in cell A1 to denote the type of import.  The cell A1 can be hidden, and protected, by simply hiding the row.

Other unused rows between row 1 and 29 can be hidden by simply selecting the rows and then selecting Excel menu “Data >> Group” or again by highlighting the rows and selecting hide:

Hiding all of the PostTrans Header fields

It is possible to put all of the PostTrans header fields in a hidden section, and then make those hidden cells reference a visible section of the template, which the users sees and enters data into.  Also the hidden header cells can contain formula making decisions based on the data entered in the visible section.   But this has the advantage of hiding the formula so the users cannot overtype them, thus protecting them.

The same protection method can be used for the line too.  For instance the columns could be defined over in columns P onwards, using formulas to reference the data entered by the user in earlier columns.  Then these columns can be hidden using the above method.

Making the sections collapsible

You may have seen that by default PostTrans makes section collapsible:

By default PostTrans groups rows 27-28 to hide the tag row, and rows 10 to 26 to hide the additional space for header fields.

To Group a section

Highlight the rows you want to make collapsible, and then select “Data” tab and hit the Group button.

To Group a section

Highlight the rows you want to un group, and then select “Data” tab and hit the Ungroup button.

Make template file read only

Simply right click on the saved template file, and select “Read-Only” option.

Protecting the sheet using Templates

The PostTrans template can be protected by simply saving as an “Excel Template” (.XLT or .XLTX), and then create a shortcut to the template for each user.  The user then simply opens a new sheet based on the saved XLT file, which is very difficult to damage since Excel will not save over the XLT file.

When the user opens “PostTransTemplate.xlt”, Excel copies the file and automatically changes the name of the new file to “PostTransTemplate1.xls” . Note that Excel has added 1 to the file name and the file is now a normal workbook. If you open  “PostTransTemplate.xlt” again, the file name will be “PostTransTemplate2.xls” etc.

You may also want to read Saving template after posting

 

More information on Excel Template files:

https://support.office.com/en-nz/article/Save-a-workbook-or-worksheet-as-a-template-58c6625a-2c0b-4446-9689-ad8baec39e1e

http://www.homeandlearn.co.uk/excel2007/excel2007s8p1a.html

Locking the Setup button

The PostTrans Setup button can be locked from the user.  To lock this button, press Setup button, and select “Options >> Lock Design”. 

The Setup button on the PostTrans look bar will be replaced with a Unlock button.  The password to unlock is “SEXONLEGS”.

Protect sections of the sheet

This can only be applied to Transaction type transactions.

It is possible to protect all cells in the sheet, except those which the user is allowed it enter data into.  To do this we select all cells:

Then right click on any cell and select “Locked”

This has now locked all Cells. 

Now we need to select each cell, range, column or row we wish to allow the user to enter data into, within our template, and then right click and un-tick the “Locked” tick for those cells.   Thus unlocking those cells.

Once we have defined all unlocked fields, we then apply the protection to the sheet, using the “Protect Sheet” option.

Then we enter a password to protect:

Enter “sexonlegs”, as PostTrans will need to temporally unlock the sheet while posting the template.   You cannot use your own password.