I have set up an excel template for use with various companies I trade with.
The template automatically displays 'xxx' where the company name should be
inserted, which then the user changes to relevant company name. However, the
company name is not always inserted by oversight and i would like to add a
prompt which appears as soon as the user creates a new document from the
template, requesting that the relevant company name be input. Unfortunately,
i have no idea how to do this!

Code like this will run each time the workbook opens.

Option Explicit
Sub auto_open()

Dim myCompanyName As String
If ThisWorkbook.Path = "" Then
myCompanyName = Trim(InputBox(Prompt:="Please enter company name!"))
ThisWorkbook.Worksheets("sheet1").Range("a1").Valu e = myCompanyName
End If

End Sub

The .path will be blank for any new workbook based on that template until the
workbook has been saved at least once. The next time you open that workbook
(not create a new workbook based on the template), the code will run, but the
path will not be "". And the macro doesn't do anything.

An alternative.

Find a cell adjacent to that company name cell.

Format it in big bold red letters:

=if(a1<"xxx","","Please enter the company name!")



ledy wrote:

I have set up an excel template for use with various companies I trade with.
The template automatically displays 'xxx' where the company name should be
inserted, which then the user changes to relevant company name. However, the
company name is not always inserted by oversight and i would like to add a
prompt which appears as soon as the user creates a new document from the
template, requesting that the relevant company name be input. Unfortunately,
i have no idea how to do this!

--

Dave Peterson