You are here:

Excel/exccel help list box

Advertisement


Question
excel worksheet
excel worksheet  
QUESTION: Dear Sir, Pls help me. I am unable to extract green colour table into Listbox with header by clicking particular Fruit list from Combobox1. For example, If I click Banana in Combox, listbox should show result:          
         
         
Pls provide the code in attached sheet

Thank in advance

ANSWER: Shah,

that is correct.  You can only get a header in an activeX listbox by using the ListIndex property to bind the listbox to a worksheet range.  It will then use the row above the bound cells for the header if you enable it.

You enablethe header with

me.listbox1.ColumnHeads = True
me.listbox1.ColumnCount = 4
me.Listbox1.ListFillRange = "Sheet2!A2:D4"

Then if you populated Sheet1!A2:D4 with the data you want an put headers in A1:D4 then you would get the result you want.

Obviously you would have to use references that match you workbook/worksheet.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Dear sir,

It has not worked. It is showing blank listbox1 and it has no connection with particular fruit in combobox

Image of Sheet1 and Sheet2
Image of Sheet1 and Sh  
ANSWER: shah,

You would need to write the code to peform the action. I just gave you some generic code to show how you would do it.  Since you are havig problems, I have set up a sheet with you situation and implemented code that does what you describe.  I have attached a picture to show the two sheets.  I am putting the data I want to display in the listbox in Sheet2 so I can link my listfillrange property to it and get the headers.  I always have the headers in A1:D1 of sheet2, so I didn't put any code in that is copying the headers from sheet1 to sheet2.  

Private Sub ComboBox1_Change()
Dim sh2 As Worksheet, r2 As Range, r As Range
Dim rw As Long, r3 As Range, cell As Range
Set sh2 = Worksheets("sheet2")
' clear data in sheet2
Set r2 = sh2.Cells(2, 1)
r2.Resize(20, 4).ClearContents
rw = 1
Set r = Me.Range("A2", Me.Cells(Me.Rows.Count, "A").End(xlUp))
For Each cell In r
 If cell.Value = Me.ComboBox1.Value Then
   rw = rw + 1
   cell.Resize(1, 4).Copy sh2.Cells(rw, "A")
 End If
Next
If sh2.Range("A1").Value <> "" Then
   Set r3 = sh2.Range("A2", sh2.Cells(sh2.Rows.Count, "A").End(xlUp)).Resize(, 4)
   Me.ListBox1.ColumnCount = 4
   Me.ListBox1.ListFillRange = r3.Address(1, 1, xlA1, True)
   Me.ListBox1.ColumnHeads = True
Else
   Me.ListBox1.ListFillRange = ""
End If
  
End Sub
   
that codes in the sheet module of your sheet with the combobox/listbox.  

that worked for me.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

listbox result
listbox result  
QUESTION: Dear sir, it has not only worked sheet2 but could not appear in listbox1 and even in sheet2, heading are also not appearing even though i have applied same code :

Dim sh2 As Worksheet, r2 As Range, r As Range
Dim rw As Long, r3 As Range, cell As Range
Set sh2 = Worksheets("sheet2")
' clear data in sheet2
Set r2 = sh2.Cells(2, 1)
r2.Resize(20, 4).ClearContents
rw = 1
Set r = Me.Range("A2", Me.Cells(Me.Rows.Count, "A").End(xlUp))
For Each cell In r
If cell.Value = Me.ComboBox1.Value Then
  rw = rw + 1
  cell.Resize(1, 4).Copy sh2.Cells(rw, "A")
End If
Next
If sh2.Range("A1").Value <> "" Then
  Set r3 = sh2.Range("A2", sh2.Cells(sh2.Rows.Count, "A").End(xlUp)).Resize(, 4)
  Me.ListBox1.ColumnCount = 4
  Me.ListBox1.List = r3.Address(1, 1, xlA1, True)
  Me.ListBox1.ColumnHeads = True
Else
  Me.ListBox1.ListFillRange = ""
End If

Answer
shah,

>heading are also not appearing even though i have applied same code

I specifically said:
I always have the headers in A1:D1 of sheet2, so I didn't put any code in that is copying the headers from sheet1 to sheet2.

so manually copy the headers to sheet2 into cells A1:D1


or use this modified code where I have manually added a line to copy the headers from A1:D1 in sheet1 to A1:D1 in sheet2.   

Private Sub ComboBox1_Change()
Dim sh2 As Worksheet, r2 As Range, r As Range
Dim rw As Long, r3 As Range, cell As Range
Set sh2 = Worksheets("sheet2")
' clear data in sheet2
Set r2 = sh2.Cells(2, 1)
r2.Resize(20, 4).ClearContents
rw = 1
Set r = Me.Range("A2", Me.Cells(Me.Rows.Count, "A").End(xlUp))

'Next line added to copy the headers
Me.Range("A1:D1").Copy sh2.Range("A1:D1")

For Each cell In r
 If cell.Value = Me.ComboBox1.Value Then
   rw = rw + 1
   cell.Resize(1, 4).Copy sh2.Cells(rw, "A")
 End If
Next
If sh2.Range("A1").Value <> "" Then
   Set r3 = sh2.Range("A2", sh2.Cells(sh2.Rows.Count, "A").End(xlUp)).Resize(, 4)
   Me.ListBox1.ColumnCount = 4
   Me.ListBox1.ListFillRange = r3.Address(1, 1, xlA1, True)
   Me.ListBox1.ColumnHeads = True
Else
   Me.ListBox1.ListFillRange = ""
End If
  
End Sub

However, if you say it doesn't copy your data then I could only guess that the values in the name section of your data do not match any of the values in combobox or your data is not where I assumed it to be.  If the latter is true, you will have to adjust the code to match you layout.  Also, I only assumed the names of the controls as combobox1 and listbox1.  There is only so much I can see in a picture - again, to adapt it to your actual situation you would have to make the adjustments.

If none of what I said helps, then perhaps you should try asking one of the other experts. I have pretty much exhausted what I can tell you and even now, my workbook that duplicated your picture is working fine.  So there is no systemic error in what I have provided. It is tested and working here.

--
Regards,
Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.