Help with a ListBox

[Guest]

I have a ListBox on a Uerform code is:

With Me.ListBox1
..RowSource = "A20:G50"
..ColumnCount = 5
End With

This works fine but it is not very neat for what I need. I need to start by
showing A20:G20 in the ListBox and then adding a row of data once the data
exists. How can I do this?

Also is it possible to format the indivdual columns in a ListBox, right
justify, center, font etc. Perhaps a ListBox is not even the right thing to
use?

TIA

Matthew

 

[Harald Staff]

Hi Matthew

I use a picture on a dialogsheet for things that has to look great,
picture's formula pointing to a decorated cell range on a hidden worksheet.
It takes lots of code and effort, and Help doesn't help with dialogsheets at
all. But it can look fantastic in the end.

HTH. Best wishes Harald

 

[Guest]

Hi Harald

That is a nice idea! The bit about lots of code and no help puts me off a
little though (bit of a newbie)! Maybe for a future project... Think I just
need to get my head around ListBoxes for now.

Thanks for the suggestion.

All the best

Matthew

 

[RB Smissaert]

A Listview has more formatting options and also has mousewheel scroll.
Slight drawback is that adding items is bit more complicated.

RBS

 

[Gary Keramidas]

not sure what you want. but maybe this will give you some ideas.

Private Sub UserForm_Activate()
Dim lastrow As Long
Dim str1 As String
Dim i As Long, n As Long
lastrow = Range("A100").End(xlUp).Row
With UserForm1

For n = 20 To lastrow
..ListBox1.Height = Application.CountA([A:G]) * 12
..Height = 15 * n
For i = 1 To 7
str1 = Cells(n, i)

UserForm1.ListBox1.AddItem Trim(str1)
Next
Next
End With
End Sub