+ Reply to Thread
Results 1 to 6 of 6

Populate Text Boxes based on Combo Box Selection

Hybrid View

  1. #1
    Registered User
    Join Date
    04-01-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    4

    Populate Text Boxes based on Combo Box Selection

    I want to be able to populate text boxes on a user form based on the selection made in the combo box. The code I have works with one exception. If the first item in the combo box is chosen, all the text boxes populate with the information from column D (so all the text boxes have the same information in them). All the other combo box selections fill the appropriate text boxes correctly. I'm not sure where I've gone wrong. Any help would be appreciated. Thanks.

    Option Explicit
    
    Dim strFind
    Dim rSearch As Range 'range to search
    Dim c As Variant
    
    Private Sub ComboBox1_Change()
    
    Set rSearch = Sheets("Copiers Master").Range("A2:A94")
    
    strFind = Me.ComboBox1.Value
    
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues, LookAt:= _
                xlWhole, MatchCase:=True)
        If Not c Is Nothing Then 'found it
            DepartmentTextBox.Value = c.Offset(0, 1).Value
            VendorTextBox.Value = c.Offset(0, 2).Value
            ModelTextBox.Value = c.Offset(0, 3).Value
            LocationTextBox.Value = c.Offset(0, 7).Value
            RenewalAmountTextBox.Value = c.Offset(0, 10).Value
            Account1TextBox.Value = c.Offset(0, 11).Value
            Account1PmtsTextBox.Value = c.Offset(0, 12).Value
            Account2TextBox.Value = c.Offset(0, 13).Value
            Account2PmtsTextBox.Value = c.Offset(0, 14).Value
            Account3TextBox.Value = c.Offset(0, 15).Value
            Account3PmtsTextBox.Value = c.Offset(0, 16).Value
            ContactPhoneTextBox.Value = c.Offset(0, 19).Value
            ContactNameTextBox.Value = c.Offset(0, 20).Value
            ContactEmailTextBox.Value = c.Offset(0, 21).Value
                    
            
        End If
    End With
    
    End Sub
    Private Sub DepartmentTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub VendorTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub ModelTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub LocationTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub RenewalAmountTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account1TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account1PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account2TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account2PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account3TextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub Account3PmtsTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub ContactPhoneTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub ContactNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    Private Sub ContactEmailTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Call Save_Changes
    
    End Sub
    
    Private Sub Save_Changes()
    
    Set rSearch = Sheets("Copiers Master").Range("A2:A94")
    
    strFind = Me.ComboBox1.Value
    
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues, LookAt:= _
                xlWhole, MatchCase:=True)
        If Not c Is Nothing Then 'found it
            c.Offset(0, 1).Value = DepartmentTextBox.Value
            c.Offset(0, 2).Value = VendorTextBox.Value
            c.Offset(0, 3).Value = ModelTextBox.Value
            c.Offset(0, 7).Value = LocationTextBox.Value
            c.Offset(0, 10).Value = RenewalAmountTextBox.Value
            c.Offset(0, 11).Value = Account1TextBox.Value
            c.Offset(0, 12).Value = Account1PmtsTextBox.Value
            c.Offset(0, 13).Value = Account2TextBox.Value
            c.Offset(0, 14).Value = Account2PmtsTextBox.Value
            c.Offset(0, 15).Value = Account3TextBox.Value
            c.Offset(0, 16).Value = Account3PmtsTextBox.Value
            c.Offset(0, 19).Value = ContactPhoneTextBox.Value
            c.Offset(0, 20).Value = ContactNameTextBox.Value
            c.Offset(0, 21).Value = ContactEmailTextBox.Value
                                  
        End If
    End With
    
    End Sub
    Last edited by Sasha28; 04-14-2012 at 06:31 PM. Reason: trying to add code tags

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Populate Text Boxes based on Combo Box Selection

    Per forum rules, please edit your post to add code tags.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Contributor
    Join Date
    09-10-2010
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2016 Office 365
    Posts
    105

    Re: Populate Text Boxes based on Combo Box Selection

    Sasha,
    I did the same thing with the code tags- the way to edit your post is place
     at the start of your code and
    at the end. That should make it work. I am not one of the guru's but I am looking at your code to see if I can help there as well.
    Juli

  4. #4
    Forum Contributor
    Join Date
    09-10-2010
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2016 Office 365
    Posts
    105

    Smile Re: Populate Text Boxes based on Combo Box Selection

    Whoops- that didnt appear right--
    [ use this bracket where it says bracket and the actual typed word code where it says code
    bracketcodebracket your code in the middle bracketforward slashcodebracket
    Last edited by Juli; 04-13-2012 at 10:08 PM.

  5. #5
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,060

    Re: Populate Text Boxes based on Combo Box Selection

    hi Sasha,
    the simpliest way is to attach a test file, remove your personal or confidential data and
    "Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button."
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  6. #6
    Registered User
    Join Date
    04-01-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Populate Text Boxes based on Combo Box Selection

    sorry about the code tags. I've revised my original post to include them. Thanks for the help.

+ 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