PDA

View Full Version : Auto fill a form



Millertant
09-28-2007, 07:14 AM
Hi All, I am trying to write a macro that will auotfill some fields on a form sheet (as in a worksheet made to look like a form not an actual form) from a second data source sheet. The data will be a large and unfriendly database dump. Users will want to plug in an ID number on their form, click a cmd button and see the generic Project Details fields auto fill based on a comparison of the ID the user entered and the ID column in the source sheet. I have attached an early test version. I have made some headway with the code but its crap, doesnt work and would almost certainly confuse matters so I haven't posted it. Will do when/if i get it anything like working. Thanks for any help in advance.

lucas
09-28-2007, 07:38 AM
I don't see an ID number on the source sheet. how is the connection made?

Bob Phillips
09-28-2007, 07:50 AM
Private Sub CommandButton1_Click()
Dim iMatch As Long

With Me

If .Range("D4").Value <> "" Then

On Error Resume Next
iMatch = Application.Match(.Range("D4").Value, Worksheets("Source").Columns(1), 0)
On Error GoTo 0
If iMatch > 0 Then

.Range("D8").Value = Worksheets("Source").Cells(iMatch, "C").Value
.Range("D9").Value = Worksheets("Source").Cells(iMatch, "D").Value
.Range("D10").Value = Worksheets("Source").Cells(iMatch, "E").Value
.Range("D11").Value = Worksheets("Source").Cells(iMatch, "F").Value
.Range("D12").Value = Worksheets("Source").Cells(iMatch, "G").Value
End If
End If
End With
End Sub


You would do well do add a Data Validation drop-down to D4 with the values for the ids.

Millertant
10-01-2007, 04:09 AM
Thanks very much, perfect!