FORMS AND DATABASE IN EXCEL

ntesic

New Member
Joined
Oct 30, 2002
Messages
1
OK Here is the quandry: I run a small call centre where Phone reps scribble down appointments on a form and hand it in to my admin lady who types it up in word then in turn we email them to sales reps who go out to appointmnents. The information is then retyped into an excel database for future reference. I know its possible to have the form in excel and have it update its own database but I cannot for the life of me work it out. Any help would be appreciated. Keep in mind that we still need to save a copy of each individual typed form that gets emailed to sales reps. Feel free to email me and i can email you the fields we require so you can get a clear picture of what we need.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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 start in AA1 with cells labled as text and lable the fields for cells (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 on the input screen. 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 each with it's own Data Form and Data Table. JSW

Use Form Buttons or hot-keys to run the macros below!

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

'Calibrate screen for scroll to DataBase-Data-Table.
Sheets("Menu").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=13

'Select first DataBase record.
Range("AA2").Select

'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.
Columns("AA:AI").Select
ActiveSheet.PageSetup.PrintArea = "$AA:$AI"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollColumn = 1
Else ' User chose No.
GoTo Can
End If
End
Can:

End Sub

Sub mySave()
'Save this workbook.
ActiveWorkbook.Save
End Sub

Sub VDBT()
'Move to the data table.
Range("AA2").Select
End Sub

Sub MainView()
'Return to the navagation menu.
ActiveWindow.ScrollColumn = 1
End Sub
 

Forum statistics

Threads
1,144,767
Messages
5,726,177
Members
422,660
Latest member
mrsteele

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 MrExcel.com.
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 "mrexcel.com".
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
Top