Vlookup combo box value to populate textbox

[Chko23]

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"

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:

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

 

[DanteAmor]

Hi and welcome to MrExcel!

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

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

 

[Chko23]

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

 

[DanteAmor]

That's right, the temp variable is declared as a Variant, since you may receive an error or data.

 

[Chko23]

That's right, the temp variable is declared as a Variant, since you may receive an error or data.

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?

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.

 

[DanteAmor]

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:

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

 

[Chko23]

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:

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

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

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

 

[DanteAmor]

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.

 

[Chko23]

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.

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 datos	Categoria	Funciona	No funciona	Colaborador	No. Sierra	No. Ticket2	Comentarios	Fecha
Avery Index to Architectural Periodicals | e10001621 |Proxy-sin descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	VERDADERO			1233	1233	04/17/2020/23:06:59
Avery Index to Architectural Periodicals | e10001621 |Proxy-sin descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	VERDADERO			12334	12334	04/17/2020/23:06:12
Access Engineering | e10004464	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
Columbia International Affairs On Line (CIAO) | e10000203 |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
E-Libro Cengage Learning DDA (Perpetuidad) (Exclusivo QRO) | e10007246 |proxy-descubridor|Querétaro, Querétaro - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
EBSCO Applied Science & Technology Source | e10001529 |proxy-descubridor|Cuernavaca, Cuernavaca - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
EBSCO Literary Refence Center | e10007155 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
EBSCO Xplora Educators Edition | e10007118 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
EBSCO Xplora Secondary Schools | e1000712x |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
Granica |e10006941 |proxy-descubridor |Puebla, Puebla Virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
IOPscience Books | e10007325 |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
IOPscience Journals | e10006667 |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
OvidSP | e10003368 |proxy-descubridor|Monterrey, Chihuahua, Ciudad de México, Guadalajara, Tec Salud	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
Periódicos Hispánicos de Estados Unidos, 1808-1980 | e1000662x |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
Science | e10007076 |proxy-descubridor|Monterrey, Santa Fe, Monterrey - posgrado virtual, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:06:12
Transactions of the ASME | e10001578 |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO			123345	123345	04/17/2020/23:06:12
Access Engineering | e10004464	LOCALES	FALSO	FALSO					04/17/2020/23:00:15
Columbia International Affairs On Line (CIAO) | e10000203 |proxy-descubridor|Monterrey, Monterrey - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:00:15
E-Libro Cengage Learning DDA (Perpetuidad) (Exclusivo QRO) | e10007246 |proxy-descubridor|Querétaro, Querétaro - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:00:15
EBSCO Applied Science & Technology Source | e10001529 |proxy-descubridor|Cuernavaca, Cuernavaca - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:00:15
EBSCO Literary Refence Center | e10007155 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:00:15
EBSCO Xplora Educators Edition | e10007118 |proxy-descubridor|Santa Fe, Santa Fe - posgrado virtual	LOCALES	FALSO	FALSO					04/17/2020/23:00:15

What do you think?

 

[WaqarCoder]

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:

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

I would really like to learn this. It is like turning excel into a real coded.