Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
As per your code PEDIDOS should be a worksheet object; declared in the
general module..Is it so??? If this post helps click Yes --------------- Jacob Skaria "Eddie_SP" wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
Yep Jacob !
It's the name of the first Sheet (Sheet1). "Jacob Skaria" wrote: As per your code PEDIDOS should be a worksheet object; declared in the general module..Is it so??? If this post helps click Yes --------------- Jacob Skaria "Eddie_SP" wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
Any help?
"Jacob Skaria" wrote: As per your code PEDIDOS should be a worksheet object; declared in the general module..Is it so??? If this post helps click Yes --------------- Jacob Skaria "Eddie_SP" wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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.Va lue, _ Worksheets("PEDIDOS").Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Yep Jacob ! It's the name of the first Sheet (Sheet1). "Jacob Skaria" wrote: As per your code PEDIDOS should be a worksheet object; declared in the general module..Is it so??? If this post helps click Yes --------------- Jacob Skaria "Eddie_SP" wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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 wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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. "Rick Rothstein" wrote: 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.Va lue, _ Worksheets("PEDIDOS").Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Yep Jacob ! It's the name of the first Sheet (Sheet1). "Jacob Skaria" wrote: As per your code PEDIDOS should be a worksheet object; declared in the general module..Is it so??? If this post helps click Yes --------------- Jacob Skaria "Eddie_SP" wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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" wrote: 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 wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
COMBOBOX SEL. / LISTBOX SHOW (VLookUp)
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 A1:Dxx to populate the combobox (but hid the B:D 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:D" & .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 Eddie_SP wrote: 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" wrote: 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 wrote: 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.Va lue, PEDIDOS.Range("A6:J2000"), 2, 0) ListBox1.Value = ComboCode End Sub But it "requires an object". Can anyone help me? Thank you in advance. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox vs Combobox | Excel Programming | |||
Listbox vs Combobox | Excel Programming | |||
comboBox vs Listbox | Excel Programming | |||
Combobox v Listbox | New Users to Excel | |||
Combobox/listbox | Excel Discussion (Misc queries) |