Solved

populate Combobox from multiColumn Text file pipe delimiter

Posted on 2013-11-25
5
444 Views
Last Modified: 2013-11-27
vba 2010
userform with
combobox1
Textbox1


Right now loading combobox from text file...but the texfile has 2 columns seperated by a pipe delimiter.

I need to load combobox with this textfile so it shows 2 columns.
Then after i make a selection...in  Textbox1 it will show only the value from Column2 in the combobox



Dim InFilet As Integer
Dim NextTip As String
InFilet = FreeFile
Open "C:\Program Files\Crs Enterprise\iData\Defaults\DescSearch_Slang.txt" For Input As InFilet
While Not EOF(InFilet)
  Line Input #InFilet, NextTip
   UserForm2.ComboBox27.AddItem NextTip
Wend
Close InFilet

Open in new window



Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39677646
load the data into an array and populate the combo with the array.

http://www.mrexcel.com/forum/excel-questions/454709-populating-multi-column-combobox.html
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39677654
they offer a soltuion of concatenating the data into a 3rd column as only one col is visible in excel... and then storing the 1st item when selected:

Private Sub UserForm_Initialize()
    Dim i As Long
    With ComboBox1
        .ColumnCount = 3
        .ColumnWidths = ";;0"
        .TextColumn = 3
        
        For i = 1 To 10
            .AddItem Range("A1:A10").Cells(i, 1).Value
            .List(.ListCount - 1, 1) = Range("D1:D10").Cells(i, 1).Value
            .List(.ListCount - 1, 2) = .List(.ListCount - 1, 0) & vbTab & .List(.ListCount - 1, 1)
        Next i
        
    End With
End Sub

Open in new window

0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 total points
ID: 39677736
So i put this in the sheet. activte to test it, and it works... showing the 2 values with a tab between them, but on selection, selects "column" 1

Private Sub Worksheet_Activate()
Dim InFilet As Integer
Dim NextTip As String
Dim col1 As String
Dim col2 As String
Dim PipeLoc As Integer

    With UserForm1.ComboBox27
        .ColumnCount = 3
        .ColumnWidths = ";;0"
        .TextColumn = 1


            InFilet = FreeFile
            Open "H:\pipeTest.txt" For Input As InFilet
            While Not EOF(InFilet)
              Line Input #InFilet, NextTip
               'UserForm2.ComboBox27.AddItem NextTip
               PipeLoc = InStr(NextTip, "|")
               col1 = Left(NextTip, PipeLoc - 1)
               col2 = Mid(NextTip, PipeLoc + 1, Len(NextTip) - PipeLoc)
               .AddItem col1
               .List(.ListCount - 1, 1) = col2
               .List(.ListCount - 1, 2) = .List(.ListCount - 1, 0) & vbTab & .List(.ListCount - 1, 1)
            Wend
End With
Close InFilet
UserForm1.Show
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39680429
Any success with the above?  you can use " " instead of VBTAB to space out the visible column...
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39681230
yes, Thanks very much !
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question