+ Reply to Thread
Results 1 to 9 of 9

AutoPopulate Texbox based on another autopopulated textbox

  1. #1
    Registered User
    Join Date
    11-29-2019
    Location
    Mexico
    MS-Off Ver
    Office 365
    Posts
    4

    Question AutoPopulate Texbox based on another autopopulated textbox

    Hello guys! I'm new to the forum and I have been learning a lot having troubles in VBA, as I know nothing about it

    That said, hope someone can help me with three things:

    I have figure it out how to populate a ComboBox and based on that selection populate several textboxes with some names. And after entering some other data I have a save button to send all those values to a table.


    But, I need those autopopulated texboxes with names, to show a value that matches that name in a table. I have tried a vlookup after the change in the combobox and also after the change in a name textbox but it does not bring the value.


    So, in the table named Tabla2 where the records are saved I have:

    Column 1 Name Column 3 Column 4 Column 5 Column 6 Column 7 Column 8
    Row 1 x John x x 123 x x x
    Row2

    If an user made a previous record and save a number that is stored in column 5, the next user that make a new record can see if that name has a record and displays it. Meaning I need to auto Populate a Texbox based on another auto populated textbox.

    To populate comboBox I have data in a Table named Bds

    Private Sub UserForm_Initialize()
    Dim cLoc As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Bds")

    For Each cLoc In ws.Range("categorias")
    With Me.ComboBox2
    .AddItem cLoc.Value
    End With
    Next cLoc
    End Sub


    To autopopulate the name textboxes I have:

    Dim ws As Worksheet
    Dim rng As Range
    Dim Sel
    Set ws = Sheets("Bds")
    Sel = Me.ComboBox2.Value
    If Sel <> "" Then
    Set rng = ws.Columns(1).Find(Sel, Lookat:=xlWhole)
    If Not rng Is Nothing Then
    Me.Reg5.Value = ws.Cells(rng.Row, "B")

    Now I need the textbox T5 to show a value that matches the auto populated Reg5. Vlookup is not working for me I do not know why. :/ Any ideas?

    Thank you so much for your help!!

    Sergio.
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,552

    Re: AutoPopulate Texbox based on another autopopulated textbox

    When you populate Reg5, also populate the other textboxes by referencing their columns.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-29-2019
    Location
    Mexico
    MS-Off Ver
    Office 365
    Posts
    4

    Re: AutoPopulate Texbox based on another autopopulated textbox

    You are absolutely right! But if I need to populate those from a table from another sheet? and to speed the search I should use the xlWhole right?

    Thank you!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,552

    Re: AutoPopulate Texbox based on another autopopulated textbox

    xlWhole versus xlPart is not a speed issue. It only depends if you're looking for a complete or partial match. You should also explicitly include the other .Find arguments in your code or else they default to the last used settings which may affect your result.

    But if I need to populate those from a table from another sheet?
    Do another .Find

  5. #5
    Registered User
    Join Date
    11-29-2019
    Location
    Mexico
    MS-Off Ver
    Office 365
    Posts
    4

    Re: AutoPopulate Texbox based on another autopopulated textbox

    Right now I have this:

    Dim fila As Integer
    Dim final As Integer

    For fila = 2 To 32000
    If Hoja3.Cells(fila, 1) = "" Then
    final = fila - 1
    Exit For
    End If

    Next

    For fila = 2 To final
    If Reg5.Value = Hoja3.Cells(fila, 2) Then
    Me.T5.Value = Hoja3.Cells(fila, 5)
    Exit For
    End If

    Next

    But I do not understand how to use the .find approach to make it look for the reg5.value and bring me the info on column 5, I also have to this for other textboxes reg6 populates t6, reg7 populates t7, and so on until reg19

    I have tried this but it does not bring me anything, it actually deletes Reg5. From what I understand it sets a worksheet(registros) then it looks reg5 on the second column and brings me what it finds in column 5, is that right?

    Set ws = Sheets("Registros")
    Sel = Me.Reg5.Value
    If Sel <> "" Then
    Set rng = ws.Columns(2).Find(Sel, Lookat:=xlWhole)
    If Not rng Is Nothing Then
    Me.Reg5.Value = ws.Cells(rng.Row, "E")

    On Error Resume Next ' if nothing found then continue
    Else
    T5.value = ""

    End If
    End If


    Thanks for your help!

  6. #6
    Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016
    Posts
    262

    Re: AutoPopulate Texbox based on another autopopulated textbox

    Place an example of your file without sensitive information. And place the code between codetags It is now unreadable.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,552

    Re: AutoPopulate Texbox based on another autopopulated textbox

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-29-2019
    Location
    Mexico
    MS-Off Ver
    Office 365
    Posts
    4

    Re: AutoPopulate Texbox based on another autopopulated textbox

    AlphaFrog thank you so much!!! That was exactly what I was looking for!!! I still have so much to learn, I'm interested about the find code, is way better than vlookup.

    Would it be a problem if I deleted the Else part of the code? I do not need the msg box as the users know that if it is empty means there is no record.

    Thank you so much!! Added to your reputation my good man.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,552

    Re: AutoPopulate Texbox based on another autopopulated textbox

    You're welcome. Thanks for the feedback.

    Delete the Else bits. No problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1