Auto run macro on File->New->Template

[owainl]

Hi,

Just quickly - I'm trying to make a template that displays a form when a user first creates a new file from it. At the moment, the macro that I'm using runs the form whenever the document is opened:

Sub Auto_Open()

JAF_Form.Show

End Sub

Thanks,

Owain

[owainl]

OK, so I've been reading about this and it now seems I should be using the following:

Private Sub Workbook_Open()

JAF_Form.Show

End Sub

Still need help making it run only once though.

[owainl]

Bump-a-rooney

[royUK]

That code will display the form on opening. You need the form & relevant code in the workbook then save it as a template (*.xlt) See

http://www.excel-it.com/excel_templates.htm

If you don't want it to show you would need to store some check in an inaccessible place, on a hidden sheet perhaps.

Private Sub Workbook_Open()
If IsEmpty(Sheet1.Cells(1,1)) Then
JAF_Form.Show
Sheet1.Cells(1,1).Value="X"
End If
End Sub

[owainl]

Cheers - smart thinking! The option I actually went for (which has now been sent out to the whole of my department, making all this a bit academic) was to have the module that loads the form delete itself afterwards. The only disadvantage of this method is that it requires users to allow Programmatic Access to VBA, thereby exposing them to a risk of virus.

I'll bear this better version in mind though and add it to the next update.

Thanks again!