Fill a form with listbox and range infomation

brendalpzm

Board Regular
Joined
Oct 3, 2022
Messages
59
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
I will explain step by step so I can explain more clear

I have this form

1701100450849.png


The data is extracted from this range

1701100534516.png


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

1701100718490.png


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

VBA Code:
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Check the answer in post #6 in this thread:

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

:giggle:
 
Upvote 0
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:

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

:giggle:
 
Upvote 0
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.
1701446169384.png


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. 🧙‍♂️
VBA Code:
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
----- --
🫡
 
Upvote 0
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. 🧙‍♂️
VBA Code:
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
VBA Code:
 nRow = CarMgmt.CarList.List(CarMgmt.CarList.ListIndex, 1)
 
Upvote 0
1701462214361.png


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

But if they are unique, then change this line:
VBA Code:
Dim nRow As Long

For this:
VBA Code:
Dim nRow As Variant

Regards
Dante Amor
🫡
 
Upvote 1
Solution

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top