Try a Listview, right click the toolbox toolbar and select additional controls, scroll down until you find Micosoft Listview Control Version 6.0 (I'm using WinXP and XL2k2, it might be different in your version), tick that and click ok. You will now have an extra control on the toolbox toolbar.
This is quite a different control to the listbox, take a look at help for some pointers on how to use it, it is essentially the same control that is used in Windows Explorer (right pane view), you can see your data in different views, you probably want lvwReport (look in the properties of the object).
I'll pop a couple of examples of using it at the end of this post, these are from a cell watch addin I wrote (before Microsoft added it to xl2002
)
The thing to note is that each row in a listview is a 'ListItem' object, and each column is a subitem (you needn't refer to col 1 as a subitem as it takes the properties of the listitem object, each subsequent column is a subitem starting at 2. Hey this isn't easy to write down, thank goodness I'm not a technical author
Try the help, MS's technical authors are much better than me, the help is pretty good.
Regards
Z
******************************
Setting up the listview at runtime:
Private Sub UserForm_Initialize()
'===================================================================
'= Procedure: UserForm_Initialize =
'= Type: Private Subprocedure =
'= =
'= Purpose: Sets up the form, listview and data on activecell =
'= Parameters: None =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |17-Jan-01| Z | Created =
'===================================================================
Dim iWidth As Integer: iWidth = zCellWatch.ListView1.Width / 6
With zCellWatch
.ListView1.ColumnHeaders.Clear
.ListView1.ColumnHeaders.Add 1, "book", "Book", iWidth
.ListView1.ColumnHeaders.Add 2, "sheet", "Sheet", iWidth
.ListView1.ColumnHeaders.Add 3, "name", "Name", iWidth
.ListView1.ColumnHeaders.Add 4, "cell", "Cell", iWidth
.ListView1.ColumnHeaders.Add 5, "value", "Value", iWidth
.ListView1.ColumnHeaders.Add 6, "formula", "Formula", iWidth
.ListView1.LabelEdit = lvwManual
End With
End Sub
***************************************
Adding items to a listview:
Sub AddWatch(objCell As Range)
'===================================================================
'= Procedure: AddWatch =
'= Type: Subprocedure =
'= =
'= Purpose: Populates the lstview object with the info about =
'= the selected cell =
'= Parameters: objCell - Range - =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |17-Jan-01| Z | Created =
'===================================================================
Dim itmX As ListItem
Dim rngCell As Range
For Each rngCell In objCell
With zCellWatch
Set itmX = .ListView1.ListItems.Add(.ListView1.ListItems.Count + 1, , ActiveWorkbook.Name)
itmX.SubItems(1) = ActiveWorkbook.ActiveSheet.Name
On Error Resume Next
itmX.SubItems(2) = rngCell.Name.Name
itmX.SubItems(3) = rngCell.Address
itmX.SubItems(4) = rngCell.Value
itmX.SubItems(5) = rngCell.Formula
.ListView1.View = lvwReport
.ListView1.SelectedItem.Selected = False
.Show
End With
Next
End Sub
*************************************
