ExcelBanter - View Single Post - Insert record into Excel database using a custom VBA form?

pr

wrote:
Rank Newbie here.
I've initialized a database area in a worksheet, and using
Edit-Define, called the area "database", and am trying to code a
custom form in VBA to insert the data from the form to the database. I
want to add a new record at the bottom of the database list. I don't
know how to code this, or find help on it. I've done lots of this in
SQL but never in VBA to an Excel database/list. Can I have some sample
code for this?

PS. I don't want to use an automatic form (Data-Forms) because a
"current date" field is in the database and I want to generate it
automatically for the user.

Assume the following

Insert a new worksheet
Name it Cust
Add 5 Field Headings in A1:E1
In the NameDefine Box enter a new name of DB
in the refers to box enter
=OFFSET(Cust!$A$1,0,0,COUNTA(Cust!$A:$A),5)

Create a new userform
Create 5 textboxes named Textbox1 to Textbox5
Create 2 Command Buttons
First button give a caption of Close and name it CmdClose
Second button give a caption of Add and name it CmdAdd

In the userform add the following code

Option Base 1
Dim NumOfFields As Integer
Dim Ar() As Variant
Dim Dbdata As Range

Private Sub UserForm_Initialize()
Set Dbdata = Range("Db")
NumOfFields = Dbdata.Columns.Count
ReDim Ar(NumOfFields)
End Sub

Private Sub CmdAdd_Click()
Dim I As Integer
Dim NewRecord As Range

Set Dbdata = Range("Db")
For I = 1 To NumOfFields
With Me.Controls("TextBox" & I)
Ar(I) = .Value
.Value = ""
End With
Next
Set NewRecord = Dbdata.Offset(Dbdata.Rows.Count, 0).Resize(1, 5)
NewRecord.Value = Ar()
End Sub

Private Sub CmdClose_Click()
Unload Me
End Sub

Display the userform add data into the textboxes and click
the Add button