Vlookup combo box value to populate textbox

C

Chko23

New Member
Joined
Apr 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone!

Newbie here,

I have a small Userform with a couple of textboxes and one combobox.

The combo box is populated from a sheet named "Nombres"

VBA Code: 
ComboBox1.List = Sheets("Nombres").Range("A1:A130").Value

So what I need is that, after the User select or change something in the combobox, the textbox "Reg4" is populated with information from a table, "Tabla2" that is located in another sheet, "Registros"

I have been trying to do it with the following code but I do not seem to figure this out:

VBA Code: 
Private Sub ComboBox1_Change()

 
 Me.Reg4.Value = WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("Registros").Range("Tabla2"), 5, False)

End Sub

Can you help me or do you have any suggestions?

Thanks in advance!

Happy to be learning vba :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi and welcome to MrExcel!

Do you have an error in the macro?
Try this:

VBA Code: 
Private Sub ComboBox1_Change()
  Dim temp As Variant
  On Error Resume Next
  temp = Application.VLookup(ComboBox1.Value, Worksheets("Registros").Range("Tabla2"), 5, False)
  If Not IsError(temp) Then
    Reg4.Value = temp
  Else
    MsgBox "The name does not exist"
  End If
End Sub
 
Upvote 0
Thank you so much! That did the trick!!!

You are declaring a variable right? Temp as Dim, is it?

Just to understand a little bit
 
Upvote 0
That's right, the temp variable is declared as a Variant, since you may receive an error or data.
 
Upvote 0
DanteAmor said:
That's right, the temp variable is declared as a Variant, since you may receive an error or data.
Click to expand...

Thank you! May I ask something else?

Now that I figure that out, I want to update those values if needed

My userform is now populated with the things that I need, how can I make some changes and send them back to that specific sheet and cell where they come from?

My button is on the sheet "Dashboard" And is retriving and sending everything to the sheet "Registros", I have tried a couple of methods but again I do not seem to be doing it right, I have this code for now, am I doing it wrong? Do you have any suggestions?
VBA Code: 
Private Sub CommandButton1_Click()

Sheets("Registros").Select

Dim rowselect As Double

rowselect = Me.ComboBox1.Value

rowselect = rowselect + 1

Rows(rowselect).Select

Cells(rowselect, 9) = Me.Reg1.Value

Cells(rowselect, 7) = Me.Reg4.Value

Cells(rowselect, 8) = Me.Reg5.Value

End Sub

Thanks in advance!

Chko.
 
Upvote 0
Ok, you already learned, a little, the vlookup function. But I suggest you use Find method.
There are other ways to search, but this time you could use Find.

Replac all your code for this:

VBA Code: 
Private Sub ComboBox1_Change()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Reg4.Value = f.Offset(, 4)
    Else
      MsgBox "The name does not exist"
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      .Cells(f.Row, "I") = Me.Reg1.Value
      .Cells(f.Row, "G") = Me.Reg4.Value
      .Cells(f.Row, "H") = Me.Reg5.Value
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Nombres").Range("A1:A130").Value
End Sub
 
Upvote 0
DanteAmor said:
Ok, you already learned, a little, the vlookup function. But I suggest you use Find method.
There are other ways to search, but this time you could use Find.

Replac all your code for this:

VBA Code: 
Private Sub ComboBox1_Change()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Reg4.Value = f.Offset(, 4)
    Else
      MsgBox "The name does not exist"
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      .Cells(f.Row, "I") = Me.Reg1.Value
      .Cells(f.Row, "G") = Me.Reg4.Value
      .Cells(f.Row, "H") = Me.Reg5.Value
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Nombres").Range("A1:A130").Value
End Sub
Click to expand...
Thank you!!! Seems to be working more efficiently, but there is one problem, whenever I update the values they are not sent to the same record. for the first record it creates another record rather than update it. And for the second and beyond, it just updates the date in the propper cell, and sends the rest to the row below that cell. But it seems to be redirecting correctly in the code. What is wrong? I added the other textboxes to update and the date in initialize

VBA Code: 
Private Sub ComboBox1_Change()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Reg3.Value = f.Offset(, 8)
      Reg4.Value = f.Offset(, 6)
      Reg5.Value = f.Offset(, 7)
    Else
      MsgBox "The name does not exist"
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      .Cells(f.Row, "I") = Me.Reg1.Value
      .Cells(f.Row, "G") = Me.Reg4.Value
      .Cells(f.Row, "H") = Me.Reg5.Value
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Nombres").Range("A1:A130").Value
End Sub
Private Sub UserForm_Initialize()
Reg1.Value = Format(Now, "mm/dd/yyyy/hh:mm:ss")
End Sub
 
Upvote 0
Do you need to have your data in a table?
You can put here a sample of your sheet "Registros"
And a sample of the data from your "Nombres" sheet

Use Xl2BB tool to show examples.
 
Upvote 0
DanteAmor said:
Do you need to have your data in a table?
You can put here a sample of your sheet "Registros"
And a sample of the data from your "Nombres" sheet

Use Xl2BB tool to show examples.
Click to expand...

Hello Dante!

Of course I can, I should mention that the find/vlookup thing is going to look for the latest value on that table(Registros), for that I have a code that sort automatically the data after a change based on the date(new to last) this in the sheet Registros, so after the date is sent to the table is automatically sorted.

For example in the table it shows the saved data from another userform, in which I can populate and modify to create a new record and sends new rows to the table. 1233 change in the new record to 1234. But when I open the userform that you are helping me with to update a record, it brings the data from the previous record(11pm) not the 11;06pm the newest. And when I send the data it creates a new record(avery index) with the proper date, but it sends the other data to another row(12345) but I need to update the last value (Avery at 11;06) changing that1234 for 12345.

Nombre de la base de datosCategoriaFuncionaNo funcionaColaboradorNo. SierraNo. Ticket2ComentariosFecha
Avery Index to Architectural Periodicals | e10001621 |Proxy-sin descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOVERDADERO1233123304/17/2020/23:06:59
Avery Index to Architectural Periodicals | e10001621 |Proxy-sin descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOVERDADERO123341233404/17/2020/23:06:12
Access Engineering | e10004464LOCALESFALSOFALSO04/17/2020/23:06:12
Columbia International Affairs On Line (CIAO) | e10000203 |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
E-Libro Cengage Learning DDA (Perpetuidad) (Exclusivo QRO) | e10007246 |proxy-descubridor|Querétaro, Querétaro - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
EBSCO Applied Science & Technology Source | e10001529 |proxy-descubridor|Cuernavaca, Cuernavaca - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
EBSCO Literary Refence Center | e10007155 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
EBSCO Xplora Educators Edition | e10007118 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
EBSCO Xplora Secondary Schools | e1000712x |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
Granica |e10006941 |proxy-descubridor |Puebla, Puebla VirtualLOCALESFALSOFALSO04/17/2020/23:06:12
IOPscience Books | e10007325 |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
IOPscience Journals | e10006667 |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
OvidSP | e10003368 |proxy-descubridor|Monterrey, Chihuahua, Ciudad de México, Guadalajara, Tec SaludLOCALESFALSOFALSO04/17/2020/23:06:12
Periódicos Hispánicos de Estados Unidos, 1808-1980 | e1000662x |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
Science | e10007076 |proxy-descubridor|Monterrey, Santa Fe, Monterrey - posgrado virtual, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:06:12
Transactions of the ASME | e10001578 |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO12334512334504/17/2020/23:06:12
Access Engineering | e10004464LOCALESFALSOFALSO04/17/2020/23:00:15
Columbia International Affairs On Line (CIAO) | e10000203 |proxy-descubridor|Monterrey, Monterrey - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:00:15
E-Libro Cengage Learning DDA (Perpetuidad) (Exclusivo QRO) | e10007246 |proxy-descubridor|Querétaro, Querétaro - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:00:15
EBSCO Applied Science & Technology Source | e10001529 |proxy-descubridor|Cuernavaca, Cuernavaca - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:00:15
EBSCO Literary Refence Center | e10007155 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:00:15
EBSCO Xplora Educators Edition | e10007118 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtualLOCALESFALSOFALSO04/17/2020/23:00:15


What do you think?
 
Upvote 0
DanteAmor said:
Ok, you already learned, a little, the vlookup function. But I suggest you use Find method.
There are other ways to search, but this time you could use Find.

Replac all your code for this:

VBA Code: 
Private Sub ComboBox1_Change()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Reg4.Value = f.Offset(, 4)
    Else
      MsgBox "The name does not exist"
    End If
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim f As Range
  With Worksheets("Registros")
    Set f = .Range("Tabla2").Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      .Cells(f.Row, "I") = Me.Reg1.Value
      .Cells(f.Row, "G") = Me.Reg4.Value
      .Cells(f.Row, "H") = Me.Reg5.Value
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Nombres").Range("A1:A130").Value
End Sub
Click to expand...
I would really like to learn this. It is like turning excel into a real coded.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Combobox not working after being modified by a macro
Replies
3
Views
95
Jeffrey Mahoney
Jeffrey Mahoney
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
289
Kscoof
K
S
  • Question Question
VBA - Populate listbox in userform based on combo box selection
Replies
1
Views
399
Saber75
S
F
  • Solved
VLookup from textbox value
Replies
3
Views
79
Joe4
Joe4
V
  • Question Question
Userform, combobox: Repicking the same selection?
Replies
2
Views
82
vrod2000
V

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

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