Assiging cell values from a Multi column combo box

[chazrab]

Is there a way to put the two values of a two column combo box on the same row the combo box is in, on a sheet with each value in a separate column, the first value of the first col of the drop down list in col A and the correstponding value in the same row of the drop down list(2nd col) in col B ?

Thanks for all your help

chazrab
CR

 

[Andrew Poulsom]

Try:

Private Sub ComboBox1_Change()
    With ComboBox1
        .TopLeftCell.EntireRow.Cells(1, 1).Value = .Column(0)
        .TopLeftCell.EntireRow.Cells(1, 2).Value = .Column(1)
    End With
End Sub

 

[chazrab]

Andrew, it works perfectly - as long as I did not press ENTER in COL E. When I combined your code with this:

Private Sub ComboBox1_Change()
    With ComboBox1
        .TopLeftCell.EntireRow.Cells(1, 1).Value = .Column(0)
        .TopLeftCell.EntireRow.Cells(1, 2).Value = .Column(1)
    End With
End Sub

Private Sub ComboBox1_Click()
ActiveCell.Offset(0, 2) = Date
ActiveCell.Offset(0, 3) = "Autodeb"
ActiveCell.Offset(0, 4).Activate
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 5 Then
        Application.MoveAfterReturn = True
        ActiveCell.Offset(rowOffset:=-1, ColumnOffset:=-4).Activate
        Application.SendKeys ("{ENTER}")
 End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next

'If Target.Cells.Count = 1 Then
'    If Not (Target.Validation.Value) Then
'        Application.EnableEvents = False
'        With Range("ALL")
'            With .Offset(.Rows.Count, 0).Range("A1")
'                .Value = Target.Value
'                .Offset(0, 1).Value = Application.InputBox("Corresponding value")
'            End With
'        End With
'        Application.EnableEvents = True
'    End If
'End If

If Target.Column = 1 Then
    With cboTemp
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 3
      .ListFillRange = ws.Range("ALL").Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    ComboBox1.DropDown
Else
  With cboTemp
  .ListFillRange = ""
  .LinkedCell = ""
  .Visible = False
  End With
End If
End Sub

pressing the ENTER key in col E to return to the row below in col A and make the combo box and drop down list again visible, I get an error dialog box that says,

Run time error 381
"Could not get the Column property. Invalid property array index",

and line

.TopLeftCell.EntireRow.Cells(1, 1).Value = .Column(0)

is highlighted in yellow. Is that line interfering with something in the rest of this code?
Under normal circumstances, without your new code, the code above allows a user to choose a value from a two column drop down list box, put the first value in col A, put the date in col C, the word "Autodeb" in col D and move to col E, waiting for the user to enter a value. When a value is entered and ENTER is pressed, the active cell moves one row below in col A and the drop down list drops down waiting for the user to enter another value. That portion works perfectly also. I just wanted to incorporate adding the 2nd cols value of the drop down list in col B.


Thanks very much for your help. I hope I have not overburdened you with too much info.

chazrab
CR

 

[Andrew Poulsom]

There is going to be a lot of interaction between the events in that code. It may be simpler to put a VLOOKUP formula in column B, eg:

=VLOOKUP(A1,ALL,2,FALSE)

 

[chazrab]

Andrew, I agree. I want to keep the coding as simple and with as few lines as possible. I was going to use VLOOKUP as a last resort because I did not want to chance having the formula accidentally erased as has happened to me using a VLOOKUP formula in cols in the past.

Many thanks for all your help.

chazrab
CR

 

[Andrew Poulsom]

If you can't use VLOOKUP because there are duplicates in the first column of range ALL, you can use INDEX to get the entry in column 2:

Private Sub ComboBox1_Click()
    ActiveCell.Offset(0, 1).Value = WorksheetFunction.Index(Range("All"), ComboBox1.ListIndex + 1, 2)
    ActiveCell.Offset(0, 2) = Date
    ActiveCell.Offset(0, 3) = "Autodeb"
    ActiveCell.Offset(0, 4).Activate
End Sub