Menu

Blog

Search

Category

AutoUpdated

Save time updating

Sage 50

Stock/Product Creation
Customer/Supplier Prices
Bill of Material
Project Creation
Location Bins

Sage 200

Simply extract, edit and import

Xero

Import from Excel

50 Trans

Nominal Journals
Quotes/Invoices/Orders
Remote Orders
Product/Stock Adjustments
Sales Receipts
Quick OrderPad

200 Trans

Transaction from Manual Input

Image Alt TextImage Alt Text

Multiple Transactions from CSV file

Xero

Solve the Puzzle?

Sage 50

Help on every type of import
Demo Video of all functionality
Loads of Examples included
Online Training
We can help too…

Sage 200

Image Alt Text

Xero

Great Customer Satisfaction

Customer Comments

Full Trial with Demo Data
Low cost Subscription
Cancel anytime
From a £1 a day
Saving you hours

Simply extract, edit and import

Power of Excel

Sage 50

Quick Template Creation
Customise with:
    Formula
    Macros
    VBA

Sage 200

To reach your goals quickly

Sage 200

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Image Alt TextImage Alt Text

Manufacturing

200 Manufact.

Create BOM Stock Easily
Create Bill of Materials
Labour, Machine, Tools

Image Alt Text

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

Image Alt Text50 Projects

PostTrans Blog

Why not sign up to recieve news, tips, ideas, training.  Sign up for bi-weekly newsletter.

Blog Search

Protecting the PostTrans template

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:

image

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:

image

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.

image

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.

image

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”. 

image

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:

image

Then right click on any cell and select “Locked”

image

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.

image

Then we enter a password to protect:

image

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

Categories: Sage Line 50, Sage Line 200, Transaction Data, Fast Orders Entry, Nominal | Tags: | Return

Post a Comment

Blog Categories