Best way to fill user form fields

Wow! It has been a long time since I've been on ExcelForum.com!

I used to consider myself pretty sharp with Excel VBA programming, but I guess I forgot a lot; I'm really rusty. I need to make a spreadsheet for use by my boss, myself and four other salespeople at work.

I have a number of questions, and I'm looking for some advice on a couple things.

My first question is related to automatically filling fields in a user form.(I've decided I don't want the user editing the spreadsheet directly.) I want the user to be able to type in a zip code, and have the city, county and state fields filled automatically (I already have the zip code database in Excel). I figure I'll use a VLOOKUP equation or the VBA equivalent, that is not the problem.

I've copied here a similar userform section of mine that allows a user to select from a drop-down box that contains people in a contact database. When they click on a person his or her data (address, phone number, etc., are filled into the appropriate fields in the user form, which when closed transfers the data to the appropriate places in the spreadsheet. It didn't always work right, and seemed like a clunky kind of way to do it.

Does anyone have any 'cleaner' way to do this?

Here is that code I was talking about:


Public Sub ComboBox1_Click()



Dim FirstLastName As Variant

If Not ComboBox1.Value = Worksheets("contactdb").Range("a340") Then




With Worksheets("contactdb").Range("c355:d2600")

Set C = .Find(ComboBox1.Value, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address

Do

Set C = .FindNext(C)

tbBillToCompany = C.Offset(0, 1)
tbBillToStreet = C.Offset(0, 3)
tbBilltoCity = C.Offset(0, 6)
tbBillToState = C.Offset(0, 7)
tbBillToZip = C.Offset(0, 8)
tbBillToContact = C.Offset(0, 2)
tbBillToPhone = C.Offset(0, 11)
tbBillToFax = C.Offset(0, 12)

Set FirstLastName = C.Offset(0, 2)


Loop While Not C Is Nothing And C.Address <> firstAddress

Else: MsgBox "Not Found"
End If

End With
End If
End Sub

Where can I get a zip code database?

Originally Posted by djvice

Where can I get a zip code database?

Finding free ones are not easy but if you PM me your email I will send you one.