+ Reply to Thread
Results 1 to 4 of 4

Autofill Colums based on combo box choice

Hybrid View

  1. #1
    Registered User
    Join Date
    08-27-2014
    Location
    Albany, NY
    MS-Off Ver
    Office 2010
    Posts
    91

    Autofill Colums based on combo box choice

    I have attached a rudimentary sheet. I was wondering how I could populate the values from Sheet2, onto the column in Sheet1, based on the combobox choice.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Autofill Colums based on combo box choice

    Sub Transfer_Answers()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
    Dim rFind As Range
    Dim strName As String
    
    strName = ws1.OLEObjects("Combobox1").Object.Value
    
    If strName = "" Then
        MsgBox ("There is no name selected")
        Exit Sub
    End If
    
    Set rFind = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strName, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not rFind Is Nothing Then
        ws1.Range("B2:B6").Copy  'your question range
        rFind.Offset(0, 1).PasteSpecial xlPasteValues, , , True
    Else
       Msgbox("That name could not be found")
    End If
    
    Application.CutCopyMode = False
    
    End Sub

  3. #3
    Registered User
    Join Date
    08-27-2014
    Location
    Albany, NY
    MS-Off Ver
    Office 2010
    Posts
    91

    Re: Autofill Colums based on combo box choice

    When this is applied to the combobox1 change event, it takes the blanks on sheet 1 and writes them to Sheet 2, leaving my data blank. I was looking for reading sheet 2 and displaying the values on Sheet 1. I'm sorry if I was not clear :/

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Autofill Colums based on combo box choice

    Sub Transfer_Answers()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
    Dim rFind As Range
    Dim strName As String
    
    strName = ws1.OLEObjects("Combobox1").Object.Value
    
    If strName = "" Then
        MsgBox ("There is no name selected")
        Exit Sub
    End If
    
    Set rFind = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=strName, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not rFind Is Nothing Then
        rFind.Offset(0, 1).Resize(1, 5).Copy 'your question range
        ws1.Range("B2").PasteSpecial xlPasteValues, , , True
    Else
       MsgBox ("That name could not be found")
    End If
    
    Application.CutCopyMode = False
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Trying to autofill two colums from one dropdown box please help
    By Aaron2104 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-26-2013, 04:56 AM
  2. [SOLVED] Autofill Based on Choice From Drop Down List?
    By jbakajust1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-01-2012, 11:57 AM
  3. [SOLVED] colums to row, autofill row with data,
    By sknifseht in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-26-2012, 11:30 AM
  4. Replies: 2
    Last Post: 07-10-2012, 04:37 PM
  5. contents of 1 combo box depends on choice of another
    By PC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM

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