Hi
I've created a worksheet which is shared amongst my colleagues with a real simply macro to show/hide rows but everytime either I or one of my colleagues opens it they get prompted to Allow marco - is there anyway for them to permantly allow this macro?
You'll want to digitally sign the project. Microsoft's information about how to do that is here: http://office.microsoft.com/en-us/excel-help/digitally-sign-a-macro-project-HA001231781.aspx
You need to change a setting in Macro Security within Excel so that your colleagues don't have to respond to a dialog box every time. Be advised, though, that when you allow macro code to run automatically, this can unsuspectingly open you up to malicious code in other Excel workbooks.
The location of the settings for Macro Security is different from versions 2003 and 2007 of Excel. In 2003 and previous, I believe the setting is visible in Tools > Macros > Macro Security. From there, you would change the security level to low. In Excel 2007 (and probably 2010), the setting is visible in the Developer tab on the ribbon (which you might have to make visible by toggling a checkbox in Excel Options). From the Developer tab, click Macro Security and chose Enable All Macros.
The above is the simplest way for your colleagues to make a change to their system, but a better way, as others suggest, would be to digitaly sign your own work so that your colleagues don't have to open a potential security hole on their own system.