Linking cells for a form

M

Melody

I have developed a form in Excel and would like when filling in the form that
it would update a database in Excel or vice versa. Is this possible in
Excel?
 
B

Bill Pfister

Melody, let me rephrase to see if I understand you correctly. You have
information on a worksheet that you would like users to modify with a form.
The form can read data from the worksheet(s) when it opens (or refreshes),
allow the user to modify in the form, and save the modified data back to the
worksheet. Is this what you would like? Can you describe more about the
type of data (single items, a one-dimensional list of values, a two-dimension
table of values, etc.).

Regards,
Bill
 
M

Melody

I want my coworkers to be able to fill out the form and have the form fill in
a database(worksheet) which I suppose would be a simple list of customers
with dates and amounts:

Name Address Account # Amount Owed Date to pay

The form is simple:

I ______(name) agree to pay ________(Amt Owed) on ______ (date to pay). For
______________ (address)on this accout #__________(account #).

I want it to fill in the worksheet but dont know how to make it continue a
list. The form information will change but want the worksheet (database) to
continually update.

does this help?
Melody
 
B

Bill Pfister

On a new sheet, create your table with the 5 fields you listed. Name the
cell of the "Name" field with the range name "FormDataX".

Caveat: there are much more robust approaches to this issue, but I get the
sense you are looking for a quick solution. Let me know if you would like
the "long version" to this answer.

Put this code behind your form:

Private Const c_strRangeNameStorage As String = "FormDataX"


Public Sub DataSave()
Dim wkb As Workbook
Dim rngStorage As Range
Dim lngLastRow As Long

Set wkb = ThisWorkbook

Set rngStorage = wkb.Names(c_strRangeNameStorage).RefersToRange
lngLastRow = rngStorage.SpecialCells(xlCellTypeLastCell).Row

rngStorage.Cells(lngLastRow, 1).Value = txtName.Text
rngStorage.Cells(lngLastRow, 2).Value = txtAddress.Text
rngStorage.Cells(lngLastRow, 3).Value = txtAccount.Text
rngStorage.Cells(lngLastRow, 4).Value = txtAmount.Text
rngStorage.Cells(lngLastRow, 5).Value = txtDate.Text

End Sub


Private Sub cmdCancel_Click()
End
End Sub


Private Sub cmdSave_Click()
Call DataSave
End Sub
 
Top