COMBOBOX SEL. / LISTBOX SHOW (VLookUp)

[Eddie_SP]

Have just started working with comboboxes and listboxes.

I have a worksheet with codes in column A and details of each code in the
other columns.

In my UserForm I have this Combobox1 that will have the values of column A
(RowSource) and many listboxes, each one for each column (B, C, D, etc).

I'm using the code below:

Private Sub ComboBox1_Click()
Dim ComboCode As String
ComboCode = Application.WorksheetFunction.VLookup(ComboBox1.Value,
PEDIDOS.Range("A6:J2000"), 2, 0)
ListBox1.Value = ComboCode
End Sub

But it "requires an object".

Can anyone help me?

Thank you in advance.

 

[Jacob Skaria]

As per your code PEDIDOS should be a worksheet object; declared in the
general module..Is it so???

If this post helps click Yes

 

[Eddie_SP]

Yep Jacob !

It's the name of the first Sheet (Sheet1).

 

[Eddie_SP]

Any help?

As per your code PEDIDOS should be a worksheet object; declared in the
general module..Is it so???

If this post helps click Yes

 

[Rick Rothstein]

If that is the actual name of your worksheet and not a variable name
containing your worksheet's name, then you need to use the Worksheets
property to reference the sheet. Try this...

Private Sub ComboBox1_Click()
Dim ComboCode As String
ComboCode = Application.WorksheetFunction.VLookup(ComboBox1.Value, _
Worksheets("PEDIDOS").Range("A6:J2000"), 2, 0)
ListBox1.Value = ComboCode
End Sub

 

[Dave Peterson]

This isn't a reply to your question, but just my own question...

If the choice in the listbox is populating the comboboxes (based on the related
value (in the same row)), why not show those values in a Label?

In fact, why not just use a multicolumn Listbox that show all the values in that
row?

 

[Eddie_SP]

Hi Rick !

Thank you for your help. I'm still trying to check things here.
Changing to "Worksheets" gave me another problem

UNABLE TO GET THE VLOOKUP PROPERTY OF THE WORKSHEETFUNCTION CLASS.

But thank you anyway. =)

Eddie.

 

[Eddie_SP]

Hi Dave !

Always giving me good ideas ! =)
Changing to a multicolumn Listbox will not help me.
I work with many document numbers, and people will have serious problems if
something go wrong.

BUT...

Changing listboxes to labels is just perfect !!!

Thank you ! =)

Eddie.

 

[Dave Peterson]

I don't understand. If you're using =vlookup() to retrieve the corresponding
values, how would that be different from showing all the values in a listbox?

But you can have lots of columns in a combobox, too. And you can choose to hide
the columns that you don't want to see. Then you can use those hidden columns
in your code--without going back to the worksheet. Then you don't have to use
=vlookup().

I put a combobox on a userform along with 3 labels (label1, label2, label3). I
used A1xx to populate the combobox (but hid the B values).

Option Explicit
Private Sub ComboBox1_Change()
Dim iCtr As Long

With Me.ComboBox1
If .ListIndex < 0 Then
'nothing selected
Else
For iCtr = 1 To .ColumnCount - 1
Me.Controls("Label" & iCtr).Caption = .List(.ListIndex, iCtr)
Next iCtr
End If
End With

End Sub

Private Sub UserForm_Initialize()

Dim myRng As Range
Dim iCtr As Long

With Worksheets("Sheet1")
Set myRng = .Range("a2" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ComboBox1
.Style = fmStyleDropDownList
.ColumnCount = myRng.Columns.Count
'show only the first column
'adjust that width to what you want
.ColumnWidths = "111" & Application.Rept(",0", myRng.Columns.Count - 1)
.List = myRng.Value
End With

For iCtr = 1 To myRng.Columns.Count - 1
Me.Controls("Label" & iCtr).Caption = ""
Next iCtr

End Sub