How to make two column combobox, show two columns after picked value !

[Petter120]

Hi !

Does anyone know how to make a combobox displays 2 column after picking a value ?

It shows 2 column in the list but once i pick a value it displays only the first column.

Best regards

Petter

[snb]

use it's property columncount=2
use it's property columnwidths e.g. 120pt;120pt

Don't be too afraid to post a sample workbook to enhance the helping process.

[MaczaQ]

Hi Petter120

try to work with that (you need sheet1 with data range a1:b10, userform, combobox1 and commandbutton1)
insert below code into your userform code

Private Sub UserForm_Initialize()

Dim iRow As Long
Dim myArray As Variant

myArray = Worksheets("Sheet1").Range("a1:b10")

With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2 ' here set index will be returned using combobox1.value
.ColumnWidths = ".5 in; .5 in"
.List = myArray
End With

End Sub

'Since I set the boundcolumn to 2, I get the second column returned. (You could
'even hide the second column by using: .ColumnWidths = ".5 in; .0 in"

'But the value is determined by the .boundcolumn.

Private Sub CommandButton1_Click()
  MsgBox "text: " & Combobox1.Text & ", Value: " & ComboBox1.Value
End Sub

[Petter120]

Sorry i thought it was just a simple adjustment.
So here is my code and i attache a sample of the workbook.
maczaq your code doesn´t work whit my.

Everything works fine in the form, the only thing i want fixed is when
i pick a value in ComboBox2 it only shows one column and i want both to show
like it is in the dropdown box in ComboBox2.

test10.xlsm

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex <> -1 Then
        Me.ListBox1.Clear
        With Sheets(ComboBox1.Text)
            Me.ListBox1.List = .Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp)).Value
        End With
        If Me.ComboBox1.ListIndex > -1 Then Me.ComboBox2.List = Sheets(Sheet_Stuff(Me.ComboBox1.Text)).Cells(1).CurrentRegion.Columns(1).Resize(, 2).Value
    End If
End Sub
Function Sheet_Stuff(ind As String) As String
    Select Case ind
        Case "Kredit"
            Sheet_Stuff = "Konto_Kredit"
        Case "Debet"
            Sheet_Stuff = "Konto_Debet"
        Case Else
            Sheet_Stuff = ind
    End Select
End Function


Private Sub Konto_Click()

End Sub

Private Sub ListBox1_Change()
    With ListBox1
        If .ListIndex <> -1 Then
            CommandButton2.Enabled = True
            TextBox1.Text = .Value
            TextBox2.Text = .Text
            TextBox3.Text = .Text
        Else
            CommandButton2.Enabled = False
            TextBox1.Text = vbNullString
            TextBox2.Text = vbNullString
            TextBox3.Text = vbNullString
        End If
    End With
End Sub

Private Sub CommandButton2_Click()
    Sheets(ComboBox1.Text).Range("A1").Offset(ListBox1.ListIndex, 0).EntireRow.Delete
    Call ComboBox1_Change
End Sub
Private Sub UserForm_Activate()
     
     'Approx over top/left cell (depends on toolbars visible)
    Me.Top = Application.Top + 500
    Me.Left = Application.Left + 1200
End Sub
Private Sub UserForm_Initialize()
    For I = 6 To Sheets.Count - 3
        ComboBox1.AddItem Sheets(I + 1).Name
    Next I
    
    With ListBox1
        .ColumnCount = 3
        .ColumnWidths = "60;"
        .BoundColumn = 1
        .TextColumn = 2
    End With
    CommandButton2.Enabled = False
    ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton1_Click()
    Dim ws As Worksheet, txtb As Control
    Dim aCell As Range, iRow As Integer

    If Len(Trim(TextBox1.Value)) = 0 Then
        MsgBox "Please enter a Account Number"
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Value)) = 0 Then
        MsgBox "Please enter Item Name"
        TextBox2.SetFocus
        Exit Sub
    End If
 
    Set ws = Sheets(ComboBox1.Text)
    
    Set aCell = ws.Columns(1).Find(What:=Trim(TextBox1.Value), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        MsgBox "Account Already Exists"
    Else
        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(1, 1) <> "" Then iRow = iRow + 1
        I = Val(ComboBox2.Tag)
        ws.Cells(iRow, 1) = TextBox1.Value
        ws.Cells(iRow, 2) = TextBox2.Value
        ws.Cells(iRow, 3) = TextBox2.Value
        ws.Cells(iRow, 4) = ComboBox2.List(I, 0)
        ws.Cells(iRow, 5) = ComboBox2.List(I, 1)
    End If
    
    '~~> Clear All textboxes
    For Each txtb In Me.Controls
        If TypeName(txtb) = "TextBox" Then
            txtb.Text = ""
        End If
    Next
    Call ComboBox1_Change
   
    
    
End Sub
Private Sub ComboBox2_Click()
    ComboBox2.Tag = ComboBox2.ListIndex
    
End Sub
    
Private Sub CommandButton3_Click()
    Unload Me
End Sub

Best regards

Petter