• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UserForm Listbox: using labels as column separator?

D

dourpil

Member
Hi !

I've tried a method to display "vertical borders" for my ListBox: Inserting labels with a width of 1.

I've tried playing with the Zorder of both my labels and my ListBox; tried Repainting the userform; all to no avail: the listbox is always sent to front.

The behavior seems erratic (I managed to have it displayed correctly, but after some changes I was unable to recreate the working scenario)


Any help on this? I also tried googling but there aren't a lot of information.
 
@Hui: I attached a file, which really is just a listbox and a label.

@vletm: Thanks for the suggestion! I'm aware of this workaround but it doesn't do in my situation: my listbox data is linked to a table and vice-versa. I'm not willing to modify my table or to change my whole code to create a temporary table with the separators :/
 

Attachments

  • Book1.xlsm
    18.8 KB · Views: 12
@dourpil: No need to modify Your table or to create something new table, 'just' add those "|" (not real border) to Your Listbox ... but not with link.
 
@vletm: Welp, I had started a reply that said "but you see, here's how I'm populating my listbox and it has more than 9 columns so I'm not using .AddItem because it doesn't work; and then sometimes I have to change the values and all my references will be moved by X columns"; but then I realized it might be easier than I thought:

So I'm creating an array with the data I need (in the code below, the visible cells of a table) and then assigning it to my Listbox:
Code: 
Dim MyData() As Variant
Dim rfilter As Range 'range to search
Set rfilter = ThisWorkbook.Worksheets("data").ListObjects("Table5").DataBodyRange
Dim Rw As Range, rng As Range
Dim Cell As Range
Dim c As Long
Dim r As Long
dim rng as range
Set rng = rfilter.Cells.SpecialCells(xlCellTypeVisible)

'Putting data in an array
For Each Rw In rng.Rows
  c = c + 1
  ReDim Preserve MyData(1 To rng.Columns.Count, 1 To c)
  For Each Cell In Rw.Cells
  r = r + 1
  MyData(r, c) = Cell.Value
  Next Cell
  r = 0
Next Rw
'Populating listbox:
Me.LbPreview.Column = MyData

So I guess I could just double the number of columns minus 1; and modify my code so that every other column gets a |.
I believe the rest of my code (modifying values, adding new rows) is NOT based on my listbox so I wouldn't have too much trouble.

Thanks for making me realize this option might be more viable than I previously thought!

Although I have to admit I'd prefer if there was a simple, effective way of drawing them as I've tried with my labels.
 
Okay, okay ...
Is it really matter how many columns there will be?
With this sample, .AddItem works!
And with minor modifications,
no need to modify Your 'source'-table as You wrote!
Code: 
Sub xxx()
    With UserForm1
        For y = 0 To 10
            For x = 0 To 4
                With .ListBox1
                    .AddItem
                    xy = x * y
                    If x = 1 Or x = 3 Then xy = "|"
                    .List(y, x) = xy
                End With
            Next x
        Next y
        .Show
    End With
End Sub

I have tried to find out 'vertical borders' too,
but that was 'the best' I have found ... I'm still searching.
 
Oh Yeah ... I found that limit too, It nice to learn almost everyday something.
Still, if anybody else have better idea. Here are two more... but ...
above: Cannot hide unwanted scrollbars ... one row activating Okay
below: Gotta find fixed width font...
I am feeling lucky that I haven't needed this kind of challenge.
more than 9 columns.png
If You'll find much better solution, please let me know too.
Good luck.
 
You must log in or register to reply here.
Back
Top