Fill a form with listbox and range infomation

[brendalpzm]

I will explain step by step so I can explain more clear

I have this form

The data is extracted from this range

when I click in the list box value it shows the following form with the values filled

but some values are not in the listbox, some of them are in the sheet range

I'm using the following code to fill the form with the listbox values

Private Sub CarList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Load UpdateRecord
UpdateRecord.UR_Name = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 2)
UpdateRecord.UR_Plates = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 1)
UpdateRecord.UR_Model = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 0)
UpdateRecord.Show
End Sub

what should I add to look for the values from the excel range as well to fill out the form?

 

[DanteAmor]

Check the answer in post #6 in this thread:

Search function for listbox userform

I'm trying to create a search function in my userform that's connected to the listbox. I have three textbox options to search from - search via surname is Textbox1, first name is Textbox2 and by year is Textbox3. I've managed to get it to kind of work, however when I add the else if's for the...

www.mrexcel.com

The option in that thread is to load the sheet row number into the same listbox.

Another option is to load all the columns in the listbox and show only the ones you need to see.


Try to adapt either of the two options.
If you have doubts, I will gladly help you adapt one of the 2 options, but you will have to put all the code of your 2 userforms to adapt them.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[brendalpzm]

is there a way to use the name as trigger to bring all the remining values, like a vlookup funciton kinda like?

Check the answer in post #6 in this thread:

Search function for listbox userform

I'm trying to create a search function in my userform that's connected to the listbox. I have three textbox options to search from - search via surname is Textbox1, first name is Textbox2 and by year is Textbox3. I've managed to get it to kind of work, however when I add the else if's for the...

www.mrexcel.com

The option in that thread is to load the sheet row number into the same listbox.

Another option is to load all the columns in the listbox and show only the ones you need to see.


Try to adapt either of the two options.
If you have doubts, I will gladly help you adapt one of the 2 options, but you will have to put all the code of your 2 userforms to adapt them.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[DanteAmor]

is there a way to use the name as trigger to bring all the remining values

In your example, I see BRENDA's name 2 times, so with vlookup or the Find method, it will always bring the data from the first record.

But I see that you have a "Row" column that apparently contains unique values.
If you have that information in the listbox, then you can use that information to find the record, which is something similar to what I recommended in the post above.

Then use the Find method to find that data in column B and thus obtain the data of the found row.

The following is an example and assumptions for filling the Type and Last Service textboxes.

Private Sub CarList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim f As Range
  Dim nRow As Long
  
  Load UpdateRecord
  UpdateRecord.UR_Name = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 2)
  UpdateRecord.UR_Plates = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 1)
  UpdateRecord.UR_Model = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 0)
  
  '
  'For example, if you have column B loaded in column 8 of your listbox...
  nRow = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 7)
  Set f = Range("B:B").Find(nRow, , xlValues, xlWhole)
  If Not f Is Nothing Then
    UpdateRecord.UR_Type = Range("G" & f.Row).Value
    UpdateRecord.UR_LastService = Range("H" & f.Row).Value
    '
    'etc...
  
  End If
  UpdateRecord.Show
End Sub

It would help if you put all the code you have, as I requested in the previous post, that way, I could adapt it to what you need.

But since you don't put all the code, I assume that with the instructions, you yourself will be able to adapt my example to what you need.



----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[brendalpzm]

In your example, I see BRENDA's name 2 times, so with vlookup or the Find method, it will always bring the data from the first record.
View attachment 102810

But I see that you have a "Row" column that apparently contains unique values.
If you have that information in the listbox, then you can use that information to find the record, which is something similar to what I recommended in the post above.

Then use the Find method to find that data in column B and thus obtain the data of the found row.

The following is an example and assumptions for filling the Type and Last Service textboxes.

Private Sub CarList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim f As Range
  Dim nRow As Long
 
  Load UpdateRecord
  UpdateRecord.UR_Name = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 2)
  UpdateRecord.UR_Plates = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 1)
  UpdateRecord.UR_Model = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 0)
 
  '
  'For example, if you have column B loaded in column 8 of your listbox...
  nRow = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 7)
  Set f = Range("B:B").Find(nRow, , xlValues, xlWhole)
  If Not f Is Nothing Then
    UpdateRecord.UR_Type = Range("G" & f.Row).Value
    UpdateRecord.UR_LastService = Range("H" & f.Row).Value
    '
    'etc...
 
  End If
  UpdateRecord.Show
End Sub

It would help if you put all the code you have, as I requested in the previous post, that way, I could adapt it to what you need.

But since you don't put all the code, I assume that with the instructions, you yourself will be able to adapt my example to what you need.



----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

I will try to do it with the Plates column since that is a unique value, in the data base is the range "D:D", and in the list box is the column number 2 (which in the code will be number 1)

I already tried like this and it says Rn-time error '13' type mismatch and it highlights this line

 nRow = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 1)

 

[DanteAmor]

In the "plates" column in your example I see duplicates.

But if they are unique, then change this line:

Dim nRow As Long

For this:

Dim nRow As Variant

Regards
Dante Amor

 

[brendalpzm]

it worked thanks !