Help with simple form

[SP]

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

[Leith Ross]

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...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Leith Ross]

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

[SP]

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

Sal