Making an Excel Database


Board Regular
May 27, 2002
Hi All,

I have recently been asked make a database in Excel. Yes, I know that Access would be the better program to use, but our firm doesn't seem to have enough Access licences, so I need a quick lesson in creating a database in Excel.

Basically, I have started by creating the user forms in VBA. That was the easy part.
However, I have only worked out how to input the data into one cell at a time.


Name: (Linked to Cell G1)
Sales Date: (linked to Cell H1)
Number of Sales: (Linked to Cell I1)

If the first person enters the data, thats fine, cells G1, H1 and I1 are filled up.

How do I then get the second persons data to go down to Rows G2, H2, and I2......and so on????

Can anyone direct me to any sites or examples on the web that I can get into and have a crack at?? It would be most appreciated,


Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You may want to try the built-in Excel Data Form for managing an Excel database. On the pull-down menu it is Data-Form...

The default form will update a database, search, querry and all sorts of things automatically. To set it up label a block of columns with your field names put the cursor in the first record(row) and activate the utility.

The code below will work with a table that is on the same sheet as the navagation menu. I use buttons and Photo objects that act as buttons to activate the code below. For your data table I start in AA1 with cells labled as text and lable the fields (AA1,AB1,AC1...)then load the table with at least one data element in AA2 (A dummy like: "First Record" or 1, anything will work.) this sets the table for the data-form defalts. The Data-Form will use your column lables as the forms field lables. It all works very slick. The code below uses the Sendkeys command to activate or use the Data-Form.

Note Data forms can display a maximum of 32 fields at one time. You can only have one Data-List per sheet, but you may have many sheets in a workbook. JSW

Sub DBForm()
'Macro made; 12/13/2001, By: Joe Was.
'Keyboard Shortcut: Ctrl+b.
'Runs the DataBase input Form.

'Calibrate screen for scroll to DataBase-Data-Table.
ActiveWindow.SmallScroll ToRight:=13

'Select first DataBase record.

'Clean Screen and show Default Data Form.
ActiveWindow.ScrollColumn = 1
SendKeys "%do"

End Sub

Sub myExit()
'Exit the database form.
SendKeys "%fx"
End Sub

Sub myClose()
'Close the datbase form.
SendKeys "%fc:"
End Sub

Sub myPrint1()
'Print the data table.
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Print Data Now?" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Ready to Print?" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Yes.
ActiveSheet.PageSetup.PrintArea = "$AA:$AI"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollColumn = 1
Else ' User chose No.
GoTo Can
End If

End Sub

Sub mySave()
'Save this workbook.
End Sub

Sub VDBT()
'Move to the data table.
End Sub

Sub MainView()
'Return to the navagation menu.
ActiveWindow.ScrollColumn = 1
End Sub
Upvote 0
I am using Excel 2007 - 1) where is the excel dataform - I can't locate it? 2) How do you make the worksheet open the dataform? I played around with the JWalk form, but I am not sure how to call it from the program.

Upvote 0
Highlight each column at the G1 cell down to the last cell being used and press Ctrl and D at the same time, this will bring the formulas down each column to link the right cells.
1. (G1)
2. (G2)
3. (G3)
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back