Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating Automatic template in excel

Status
Not open for further replies.

Benplayford

Technical User
Dec 7, 2001
2
GB
Can anyone help me?

I want to create a template in excel, much the same way as a letter template can exist in word. What I mean is that once the file is selected you then have to input certain information fields which are then automatically entered into the correct areas of the workbook, thereby eradicating any duplication of entering the same in formation in two or more different places.

My VBA knowledge is limited so I was wondering if there was a quick way of doing this or whether someone could point me in the direction of how to start the process.

Responses would be welcome,
Thanks
 
Hi,

This code will give you the basics (and it is very basic!)...
Insert this code into the ThisWorkbook object,, it will run automatically every time the workbook is opened...

Private Sub ThisWorkbook_open()
activesheet.range("A1").value = inputbox("Name?", "My Template", "Your Name")
activesheet.range("B1").value = inputbox("Age?", "My Template", "Your Age")
activesheet.range("C1").value = inputbox("Sex?", "My Template", "Yes! hehe")
end sub

If you adjust the ranges, input boxes and sheets then you should have the bare bones of what you need. You might need some sort of validation to check that the user has actually entered some data, so maybe put a small loop around each of the lines above ie.

do
activesheet.range("B1").value = inputbox("Age?", "My Template", "Your Age")
loop until activesheet.range(&quot;B1&quot;).value <> &quot;&quot;

doing this will continue the user prompt until they actually enter something. If you get a bit cleverer, you can make sure that they're entering data in the correct format as well, check this up in the VBA doco for more info...

Hope this helps you on your way,

Kaah.
 
Hi,

This code will give you the basics (and it is very basic!)...
Insert this code into the ThisWorkbook object,, it will run automatically every time the workbook is opened...

Private Sub ThisWorkbook_open()
activesheet.range(&quot;A1&quot;).value = inputbox(&quot;Name?&quot;, &quot;My Template&quot;, &quot;Your Name&quot;)
activesheet.range(&quot;B1&quot;).value = inputbox(&quot;Age?&quot;, &quot;My Template&quot;, &quot;Your Age&quot;)
activesheet.range(&quot;C1&quot;).value = inputbox(&quot;Sex?&quot;, &quot;My Template&quot;, &quot;Yes! hehe&quot;)
end sub

If you adjust the ranges, input boxes and sheets then you should have the bare bones of what you need. You might need some sort of validation to check that the user has actually entered some data, so maybe put a small loop around each of the lines above ie.

do
activesheet.range(&quot;B1&quot;).value = inputbox(&quot;Age?&quot;, &quot;My Template&quot;, &quot;Your Age&quot;)
loop until activesheet.range(&quot;B1&quot;).value <> &quot;&quot;

doing this will continue the user prompt until they actually enter something. If you get a bit cleverer, you can make sure that they're entering data in the correct format as well, check this up in the VBA doco for more info...

Hope this helps you on your way,

Kaah.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top