Send Data from user form to a data base

Hello.

I created a simple user form(actually my first one! )in which the user will fill some basics info(name, phone..etc).

I created a button that calls this user form in a sheet.

I have no idea how the filled in user form data, should transfer in the first available row in another sheet(database),then clean the user form from the data and do it again.

Can you help me on this pls?

Thanks in advance!

Gali spera Maria.

This code is over kill for what you want at the moment. But I'd use it anyway as you will need something like this.

Private Sub CommandButton2_Click()


Count = 1
Sheets("DataBase").Select

'Finds the last used row in column A and gets the row number of the cell below that
NextRow = Range("A1").End(xlDown).Offset(1, 0).Row

'Creates an array
Dim myArray As Variant

Makes the array big enough to take all your data based on the number of items in the userform
ReDim myArray(Me.Controls.Count)


'This loops through all the items in the userform
Dim cCont As Control

    For Each cCont In Me.Controls
    
'we are not interested in labels or frames so ignore them
If TypeName(cCont) <> "Label" And TypeName(cCont) <> "Frame" Then

'Save the value into our array
 myArray(Count) = cCont.Value

increment the count so the next value goes into the next array slot           
Count = Count + 1
End If
10      Next cCont



'Manipulate columns like this:-   This lets you change the order that the data is written to the spreadsheet
myArray(0) = myArray(5)

'This line writes all your data to excel.  It is fast and one of the reasons I use an array, the other reason is that it is easy to manipulate the order of the data.
Range(Cells(NextRow, 1), Cells(NextRow, Me.Controls.Count)).Value = myArray

End Sub

Kali spera mehmetcik!

Thank you for your code. I am sure-as it's my first time that i work with UserForm- that i do something wrong because your code gives me errors(overwrite the results, gives a false value in columns G:H).

Holger

Thank you .Your code do exactly what i had in my mind! May i ask 2 questions pls?

Dim lngCount As Long
Dim lngLR As Long

What these 2 lines do?

.Cells(lngLR, 1 + lngCount).Value = Me.Controls("Textbox" & lngCount)
    Me.Controls("Textbox" & lngCount) = vbNullString
  Next lngCount

This one -as i understand-control all the text boxes-as i haven't rename these?

I ask this because my intention is to use another userform to transfer data in columns G:H & K:L, on DataBase sheet. Will i use the same logic for doing this? or it's something completely different?

Thank you so much!

Appreciate your feed back with your explanations. I'll try to use these in my new try. If i have problems i know where you are..!

Thank you once again!

Hi Maria

attached is my version of your workbook.

I don't get the error you reported.

I know you have a working solution but my version gives you a lot of powerful tools.

So it is worth studying.

Hi mehmetcik.

Thank you. As i told probably i was doing something wrong. Your code works fine. 2-3 questions if you don't mind.

Can we do something for column G:H that gives us a FALSE value?

What do we have to change so data to be transfered from row 2 and down-row A will be the headers!

What if we need to transfer the zip number to column M?

Thanks again.

[QUOTE=MariaPap;3488541]Hi mehmetcik.

Can we do something for column G:H that gives us a FALSE value?

Naí, I have added code to loop through the array and replace "False" with ""

What do we have to change so data to be transferred from row 2 and down-row A will be the headers!
Nothing. Just put your titles in row A.

The macro looks for the first empty row.


What if we need to transfer the zip number to column M?

That is your array manipulation.
M is column 13 which is Array Entry 12 ( Arrays start at 0 )

so

Myarray(12) = Myarray(4)

moves the zip to column M

and

Myarray(4) = ""

clears the old position.