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
Bookmarks