Creating Automatic template in excel

[Benplayford]

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

 

[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("A1&quot.value = inputbox("Name?", "My Template", "Your Name&quot
activesheet.range("B1&quot.value = inputbox("Age?", "My Template", "Your Age&quot
activesheet.range("C1&quot.value = inputbox("Sex?", "My Template", "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("B1&quot.value = inputbox("Age?", "My Template", "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.

 

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