I am writing a simple form which displays a list of names in a ComboBox
and then populates the text boxes on the form, problem is the forms
works for the first choice but fails after that.

I stepped through the code and I see that the .ListIndex value is
correct only the first time.

Am I doing this the wrong way, in the end I just want a simple form to
view, edit and add records.

Thanks in advance.
Sal


The spreadsheet is labeled "facilities" and is populated:
---------------------------------------------------------
Line Facility Street City ST ZipCode
1 Copps 3 Maple Mystic CT 06845
....
168 G.Acres Ryan Ave Canaan CT 06018


The code is:
------------
Option Explicit
Dim facilities_count As Integer
Dim facilities_current As Integer
Dim rng_facilities As Range

Private Sub cmd_Close_Click()
Unload Me
Worksheets("Facilities").Activate
End Sub

Private Sub txt_Facility_Name_Change()
' Write value back to cell

End Sub

Private Sub txt_Facility_Name_DropButtonClick()
Dim i As Integer
Worksheets("Facilities").Activate
facilities_count = ActiveSheet.Cells(600, 1).End(xlUp).Row
For i = 2 To facilities_count
txt_Facility_Name.AddItem (ActiveSheet.Cells(i, 2))
Next i
End Sub

Private Sub txt_Facility_Name_Click()
' Get index number of selected record
<<<< the next line is the bad code
facilities_current = txt_Facility_Name.ListIndex + 1
populate_facilities
End Sub

Private Sub populate_facilities()
Set rng_facilities = Range("A2", "I300")
txt_Facility_Street.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 3, False)
txt_Facility_City.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 4, False)
txt_Facility_State.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 5, False)
txt_Facility_Zip.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 6, False)
txt_Facility_Phone.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 7, False)
End Sub

Hello SP,

The ListIndex property is updated when the user selects an item in the
ComboBox List. ListIndex is a subproperty of the List property. You
code should read...


Code:
--------------------
Private Sub txt_Facility_Name_Click()
' Get index number of selected record
With txt_Facility_Name
facilities_current = .List(.ListIndex) + 1
End With
populate_facilities
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479989

Just a heads up

Here we see he populates the combobox with the names of the facilities in
column B

txt_Facility_Name.AddItem (ActiveSheet.Cells(i, 2))

here we see his lookup range starts in column 1 which if you look back at
his post contains sequential numbers beginning with 1

Set rng_facilities = Range("A2", "I300")
txt_Facility_Street.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 3, False)


So he should be using the listindex property as he currently is doing

----------------------------

I think, since he loads data into the Combbox on the DropButtonClick event,
he needs to add a line to remove the existing values

Private Sub txt_Facility_Name_DropButtonClick()
Dim i As Integer
Worksheets("Facilities").Activate
facilities_count = ActiveSheet.Cells(600, 1).End(xlUp).Row
'
' Add the next line
'
txt_Facility_Name.Clear
For i = 2 To facilities_count
txt_Facility_Name.AddItem (ActiveSheet.Cells(i, 2))
Next i
End Sub

--
Regards,
Tom Ogilvy




"Leith Ross" wrote
in message ...

Hello SP,

The ListIndex property is updated when the user selects an item in the
ComboBox List. ListIndex is a subproperty of the List property. You
code should read...


Code:
--------------------
Private Sub txt_Facility_Name_Click()
' Get index number of selected record
With txt_Facility_Name
facilities_current = .List(.ListIndex) + 1
End With
populate_facilities
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479989

Hello Tom,

Good catch on clearing the Drop Down. When I posted my response, I had
forgotten the Drop Down was being reloaded each time the macro was
called. Sorry Sal if I caused any you problems.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479989

Leith, Tom
Thanks for the answers, this simple stuff is harder than it looks.

Sal