Linking a form to a database

[emipuss1]

I am having a major blonde moment!

Here's my problem,

I have an electronic form that our billing/shipping department fills in which is "supposed" to make it to our finance department. Problem is that is disappears!

So I was to create a 'log sheet' that is automatically updated. When the person opens the electronic form and fills in the necessary cells, I want them to be able to push a 'button' and all the pertient information is automatically copied onto the logsheet (located in a seperate file). But I want the logsheet to be able to coundt down, therefore adding line by line.

In my head this works, but actually doing it is another question.

Sorry about the long windedness...been working on this for a while!

 

[fneily]

I have one excel file which is my history file. It simply has my headings such as:
Employee Name: Employee ID: Department: Start Date:
End Date: Office Phone: Reason: Date Entered No. of Days

The above headings are on one row.

In another file, which the users input their data, I have corresponding headings and empty cells below the headings for their input.

In VBA (ALT + F11), on the left, expand VBAProject(YourWorkbookName), then double click on ThisWorkbook. Type in the following, substituting your cell names and worksheet name, to clear out the previous data:
Private Sub Workbook_Open()
Worksheets("main").Range("A7").Value = ""
Worksheets("main").Range("B7").Value = ""
Worksheets("main").Range("C7").Value = ""
etc.
End Sub

Create button and code:: Click View - Toolbars - Control Toolbars. Create a Command Button. While button is selected, right click and select View Code. In between the Sub and End Sub statements, you will place code such as the followingobviously, change cell references, file names, etc. to yours)
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Set mybook = Workbooks.Open("C:\Berry_History_Book.xls")
Range("B4").Value = mybook.Worksheets(1).Range("A20000").End(xlUp).Row + 1
NewRow = basebook.Worksheets(1).Range("B4").Value
Set sourceRange = basebook.Worksheets(1).Range("a7:g7")
Set destrange = mybook.Worksheets(1).Cells(NewRow, 1)
sourceRange.Copy destrange
Set sourceRange = basebook.Worksheets(1).Range("g4")
Set destrange = mybook.Worksheets(1).Cells(NewRow, 8)
sourceRange.Copy destrange
Set sourceRange = basebook.Worksheets(1).Range("B7")
Set destrange = mybook.Worksheets(1).Range("K2")
sourceRange.Copy destrange

mybook.Worksheets(1).Range("K3").Copy
basebook.Worksheets(1).Range("E9").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

Range("E8").Copy
mybook.Worksheets(1).Cells(NewRow, 9).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
mybook.Close True
Application.ScreenUpdating = True

Then close VBA. Close the Control Toolbox. To Edit the code for the command button, bring up the control toolbox again and click on the Design mode button, then right click the command button and reenter VBA.

 

[emipuss1]

Thanks for your help, but I have one problem/question.

On which file are you putting this first set of instructions? On your sheet or the one that others use?

 

[fneily]

You mentioned you want two FILES. Neither goes on a sheet. Both codes go in the users file. The first lines of code just sets all your input cells to null and goes on the workbook's open event. The second set goes on your command button's onclick event.
Now, this code does alot more then what you want. So work through it and modify it for your needs.
This is the line that finds the last row to insert the new data:
Range("B4").Value = mybook.Worksheets(1).Range("A20000").End(xlUp).Row + 1

It starts at A20000 then goes up (xlUp), bumps a row with data, then adds 1 to go to the next empty row.

 

[SkipVought]

[red]
Please post VBA related questions in Forum707.[/red]

Skip,

_[red][/red]