Insert record into Excel database using a custom VBA form?

[Guest]

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.

 

[Guest]

This assumes that the UF has a text box into which the new entry is typed
named "TextBox1" and that there is a command button named "CommandButton1".
This command button is clicked to add the new entry to the end of the db. The
db is assumed to be in column A of "Sheet1" and all cells below the db in
column A are empty. Therefore, the cell identity for the new entry is
determined by finding the last entry in column A and offsetting 1 cell down.
Note that the named range is therefore unnecessary.

Paste the following to the UF code module:

Dim c As Range
Private Sub UserForm_Activate()
GetNewEntryAddress
End Sub

Private Sub CommandButton1_Click()
c.Value = TextBox1.Text
GetNewEntryAddress
End Sub

Private Sub GetNewEntryAddress()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Set c = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

 

[pr]

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

 

[Guest]

thanks, guys!

I'll try out the code. Nice to have people respond this way.