I created a userform and Excel spreadsheet. Now I want to collect the information from this form to create a database of all customers that fill out t

[Scotstang]

I created a userform and Excel spreadsheet. Now I want to collect the information from this form to create a database of all customers that fill out the form.

Also made a Criteria Range on a separate sheet and then a Table based on this Criteria range. I then placed formulas directly under each header, corresponding to the Estimate Form created by the UserForm. Then I created a Macro that copied the cell values of each column to the table and then hit tab on the last column and then stopped the macro. I then pasted this macro to the userform hoping that each time I hit close on the userform that the values would not only be placed in the Estimated form for the client to see but also into the Table/Customer Database. This way my database will be populated as people seek estimates for possible purchases.

Current Customer's Name/ Address/ State /Zip /Customer / Model/ CD Player/ Power Doors & Windows/ Remote Starter/ Sunroof/ 4X4 /KitCar Estimate
Joe Schmeling /Max Louis Drive /SD /9877 /Pick-Up/ $200 / $- /$650 / $850 / $-/ $21,200

this is what the Criteria Range looks like (1st Row) and corresponding Values (second Row) I Separated them using the "/" symbol.

Ok now that ive thouroughly confused you too, Is there anyone that can tell me how I can create a database from names that are submitted from my UserForm in VBA?

 

[rs2k]

Welcome to the board,

If I understand you correctly, you want to send the data from the userform to 2 separate worksheets? (one for estimates and one for your client database)

Add a bit of code to send it to the second sheet, below is a bit of code for sending to 2 ws,

Private Sub cmdSubmit_Click()

Dim irow As Long
Dim ws As Worksheet
Dim ws2 As Worksheet

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
irow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Application.ScreenUpdating = False

With ws
.Range("A" & irow) = txtBox1.Value

End With
With ws2
.Range("A" & irow) = txtBox1.Value

End With
txtBox1.Value = ""

Application.ScreenUpdating = True
Unload Me

End Sub

Hope this gets you started, or post back with what you want to happen and any code you already have,

HTH
Colin

 

[Scotstang]

Hey Thanks for the reponse.

I think that I already have the problem of getting the data to the Estimate Form solved as I had already assigned the cells that I wanted the userform to write to. I then took those cells and created, on a separate worksheet, a linked group of cells, referencing the estimate form for values. So as the Userform is used and the customer values are entered, the values are placed in the estimate form and also copied to the "Customers" worksheet. I then copied this row of headers along with a row containing data and used it to create a Table 3 rows directly under the criteria range at the top of the worksheet. This Table contains the same headers as the criteria range at the top of the page does. What I am working on now is a macro that will copy each new estimate made from the userform and send it to the Table, thus creating a list of customers and the estimates that they have recieved.

I now need to find out how to copy the group of cells Range(A3:K3) to the first empty row within the Table. The first available row in the table are cells Range(A7:K7). As Customer names are added this value will change, First to Range(A8:K8) then Range(A9:K9) and so on and so forth. My macro simply needs to find the first empty row Range(A?:K?) to Paste the customer Estimates too.