Data entry with a new Worksheet

[Ron M]

I am creating a template for scheduling the visits of a work van to various remote sites (towns, villages, etc.) outside of the city. I have most of the scheduling calculations working correctly, but I also want to be able to put some site specific information on the ouput below the schedule. For example, Site Name, Site Location, phone, fax, Site contact, etc. I am hoping I can create a user input form that opens when, and only when a new document (workbook)is created -- I don't want it opening everytime the spreadsheet is opened. I want to be able to prompt the user to enter all of this information on a data form and then have it populated to the appropriate named range (single cell). I am hoping someone can help me, or point me in the direction of some assistance. Thanks in advance.

Ron M [img]/w3timages/icons/smile.gif[/img]

[Hans Pottel]

Ron,

You can attach a macro to the template that run when the template is opened (workbook_open event). This macro can fire a dialog form in which the user fills in the requested information. Of course you should build in code that looks if the information is already there or not, because next time you open the document, the workbook_open event will run again. e.g. you can put the information on a fixed place on the worksheet and let the code check if this information is there. If not, the dialog box appears, else, nothing happens. Don't know if this was what you had in mind?

[Ron M]

I think you are definitely on the right track. Being a VBA newby, I was wondering what some of the code might look like. I think I can build the UserForm, but I am unclear as to what the code looks like to get the values from the UserForm to named ranges on the spreadsheet. As far as having this happen everytime the workbook opens, is there such a thing as an Autonew macro that would only open when you use the template to create a new document?

Thanks for your help.

Ron M

[Hans Pottel]

Ron,

Add a userform (in the VBE editor, choose Insert >> UserForm) to your template, and put something like this in the Workbook_Open event of ThisWorkbook (to find this: in the VBE editor, choose the Project Explorer (if not present, choose View >> Project Explorer to make it visible) and go to ThisWorkbook under Microsoft Excel Objects, there you should find the right dropdown box with General and Workbook; in the left dropdown box, you will find the Open event for the Workbook):

In the code below, I just check if something was written in the first cell of the range R (which is here defined as Sheet1!A1:A10). If the cell is not empty then you go on with whatever you want (actually nothing should happen then in your case, here I let appear a messagebox); in the other case the Userform appears.
Private Sub Workbook_Open()
Dim R As Range
Set R = Range("Sheet1!A1:A10")
If Not IsEmpty(R.Cells(1)) Then
MsgBox "DataForm is already present"
Else
UserForm1.Show
End If
End Sub

[Hans Pottel]

To get your data from the form into the spreadsheet, you can use textboxes on the form and use e.g.

Sheets(1).Cells(1, 1).Value = TextBox1.Value
Sheets(1).Cells(2, 1).Value = TextBox2.Value

This will put the contents of the textboxes in the cell A1 and A2 on the worksheet with index = 1. You have to experiment a little bit with these things and you'll see that it is not difficult at all. There are several other ways to put the contents of textboxes into cells, using the range method.

[rory]

Hi Ron,
I think Hans has sorted out your code for you (you can incidentally set the controlsource of your textboxes to a cell address and this automatically enters whatever you put in your textbox into that cell without any additional code) but as regards your autonew macro, I think your simplest solution is to run the code from a second workbook which then creates new workbooks based on your template. Because the code is in the second workbook rather than in the template, it does not get included in the created workbooks.
Hope that helps.

[Ron M]

Thanks to all of you.[img]/w3timages/icons/smile.gif[/img][img]/w3timages/icons/smile.gif[/img]
I shall have to sort some of this out. I like Rory's suggestion of having the control source for the text boxes tied directly to a single cell. I have one question about this...can you also tie it to a named range, where the named range is a single cell. For example, I have a single cell that I have named "Destination". I want the UserForm to have a text box labelled "Destination" tied to the cell named "Destination" on the worksheet. Is this possible? Thanks.

Ron M

[rory]

Ron,
I like questions like that: the answer is yes! [img]/w3timages/icons/grin.gif[/img]

[Ron M]

Thanks for everyone's help. I can get the UserForm to work, puts data in the right place. The one thing I cannot make work is code in the Workbook Open subroutine. I may not have it set up correctly. When I open a new workbook using the template, it doesn't even know the code is there. Any hints or thoughts as to why this happens or doesn't as the case may be?

Thanks to all.

Ron M

[LegareColeman]

You need to use the NewWorkbook event to catch when a workbook is created from a template.

[Hans Pottel]

Ron,

I don't understand. I made templates with code in the workbook_open event several times.
Add some code to the Workbook_Open event of ThisWorkbook of a blank workbook. e.g. simply add MsgBox "Hello", then, save it and open it again. The messagebox should appear. If this is OK, save your workbook as a template (.xlt). Then make a new workbook via File >> New and select your template. The messagebox should appear and the code will be attached to your workbook. If this doesn't work, then why does it work with my Excel version (using Excel 2000 under Win 98).

[Ron M]

As a VBA newby, I am still learning a lot, can you tell me how this might work. Is this something that goes into an if statement...

If NewWorkbook.Open, then...or would you have
SUB NewWorkbook_Open() instead of Sub Workbook_Open()

Thanks.

Ron M

R[img]/w3timages/icons/smile.gif[/img]

[Ron M]

Hans, I will try what you suggest and simply put a msgbox inside the subroutine and see what happens. Strange thing is that I can bind the macro to a button on the spreadsheet and click on the button to get the UserForm to display, but not from the Workbook_Open -- go figure?? [img]/w3timages/icons/crazy.gif[/img]

Thanks for the suggestion.

Ron M

[LegareColeman]

Actually, I can't figure out how to make it work. When I wrote my original reply, I found this event in the Excel 2000 help files. However, the information there is VERY skimpy, and it does not say anything about how to implement it. I had assumed, without looking, that it was one of the event procedures in the Workbook code module. However, on looking there, I do not find a procedure for this event. I also tried just putting a procedure with the name given in the help files into a normal module, but that did not work. So, I am beginning to think that the reference in the help files is an error.

However, I also checked, and in my copy of Excel 2000, the Workbook Open event does work when I create a new workbook from a template that contains a Workbook Open event procedure. I put a MsgBox statement in a Workbook Open event procedure, stored the workbook as a template, and when I cerated a new workbook from the template the MsgBox was displayed. Therefore, I do not understand why it is not working for you. Can you describe exactly what yuou did to create your template?

[Hans Pottel]

Legare,

The NewWorkbook event is an Excel.Application event, which you have to make work via class modules.