Text wrapping in a listbox control

[chazrab]

Many through the years to the present wish this were possible. That is for Excel's Listbox have the ability either with Properites or coding
to display selected items in a paragraph(i.e., text wrapping) instead of 1 line per row, even though the underlying cell values have their alignment property set to wrap the
text in the cell( It doesn't show that way in the Listbox view, as we all know.

Please see images below. One is a textbox displaying FIND method results; the other is a
Listbox displaying the same FIND method results but all results are always on ONE LINE. The fact that they both appear with spacing should be ignored for this
question. In both the textbox and Listbox that's easy enough to accomplish with code or just by inserting blank rows on the underlying sheet.

Many answers to this question have suggested something called a "DataGrid Flex control' that they feel could and would easily accomplish this task.
What in the world is a DataGridFlex control? I cannot find it in either of the Excel Controls toolboxes.

The one big advantage I see of a listbox over a textbox for displaying records is that Listbox records can be easily linked to other data in columns to the right on the sheet
and results of long text lines can be displayed in a textbox.
Textbox items have, to my knowledge, no way to be identified as singular entities because they don't occupy their own rows.

Sorry for long explanation. Any help would be greatly appreciated.

cr

 

[Micron]

The situation is not likely to ever change because the listbox and combobox controls are for showing list items, not data, which is what you're trying to do. If this were Access I'd tell you to use a form with a datasheet view. Since it's not, I don't know what else to say except that the only way to increase the row height is to increase the font size. That will not result in what you're after though.

 

[chazrab]

Good feedback - as I expected. Thanks. Too bad there's not a way to do this

Private Sub ListBox1_Click()
Dim n As Long
    n = ListBox1.ListIndex
    TextBox4.Value = ListBox1.List(n, 0)
    TextBox5.Value = ListBox1.List(n, 1)
    TextBox6.Value = ListBox1.List(n, 2)
End Sub

with Textbox items separated by spaces as I have in the first image. I thought about using Len and creating an index for each separate item in the
textbox, where the index would create a unique link, but I'm not sure this can be done with spaced paragraphs in a textbox.

Thanks again, Micron
cr

 

[Micron]

I can think of something that might work but its' a real hack because Excel really isn't meant for this sort of thing. It's my go-to app for charts and complex sheet calculations, but it's not a database by any means. IMO, a db is what you need for this.

That hack would be along the lines of stacking a whack of hidden textboxes on top of each other (how many are in a 'whack' anyway?) and then making them visible where they need to be placed by altering their left and top values. What drives that would be the number of rows/lines of text you need to show. I've done that for an Access form as a solution to a thread where the OP wanted to show only applicable textboxes to different users but not have gaps where the invisible ones are, and to not reveal their presence by just disabling them. Was slick, but makes more sense for a db I think.

It seems I've read that there is a sheet control in Excel. Could you not use that as a 'datasheet' on your form and resize rows according to the cell contents? Just grasping at Excel straws here.

 

[chazrab]

I'm not familiar with a sheet control. I'll look it up. As I build this app, I'm discovering I'm reaching the edge of Excel's functionality in what
I want to accomplish.
I'm leaning back to using a Listbox at this point because it allows establishing relationships with data on the same row in different columns
by simply using

 n = ListBox1.ListIndex
TextBox4.Value = ListBox1.List(n, 0)
 TextBox5.Value = ListBox1.List(n, 1)

As mentioned in the beginning, the big drawback for me with a Listbox control, is that it does not give a pleasing view of displaying text
as does a textbox - mundane one-line rows all jammed together.

Thanks again for all your help.
cr

 

[Micron]

As I build this app, I'm discovering I'm reaching the edge of Excel's functionality in what
I want to accomplish.

Time to upgrade to Access?
Seriously though, if you take that route you will find that either you follow the big learning curve or you will probably struggle. Do not approach a db as you would a workbook. Good luck!

 

[chazrab]

Hi Micron - thanks for comments and willingness to help me.

I'm familiar 'with dbs and have used Access many times, mainly for importing and exporting data to and from Excel. But I've not written code in and for an Access app (yet). If the process is the same as writing VBA code in Excel, I shouldn't have a long
learning curve.
Because O365 apps work together I probably will try to port (export) what I can of my Excel app
over to Access and continue from there. From what I know, this would just involve exporting .xlsm sheets each with 31103 rows of data in each of 4 cols
A,B,C and D over to an Access db. It sounds like you know both Excel and Access pretty well so you should be able to speak to this.

That said, some questions pop up in my mind. (1) Does Access have some sort of Listbox linking capability like Excel but with the ability
to wrap text or use Multiline text for each db row in Access?

Then,, (2) is it possible to export objects, i.e., userforms and buttons to Access with their underlying code and use the same
VBA code within Access to continue my programming?

If I can do all that and more with Access, then I'll just port this entire app over to Access.

Below are images of a sheet and userforms which mainly display text from sheets and FIND method search results. Just want to be able to do this
same thing (the GUI) in Access if I can, but with dbs instead of sheets in the underlying data.

Some of the images below on this app may appear simple and redundant. I assure you, they're not. Each colored button performs a function
and displays specific searched for or historical data, all from underlying SHEETS. Theses sheets would all have
to be exported to Access dbs. If all this is definitely possible in Access, I'll start the process today.

It'll give me a good opportunity to bone up on Access - something I haven't had to do for a while but may be necessary now.

Thanks again for all your help.

cr
Kingwood, Tx

 

[Micron]

From what I know, this would just involve exporting .xlsm sheets each with 31103 rows of data in each of 4 cols
A,B,C and D over to an Access db.

Almost never, because spreadsheets do not usually make for good tables. Yours might be an exception or your level of Access expertise would allow you to fix the problem. A lot of people dive in to Access without understanding db normalization and the nature of relational databases. I see it all the time at Access Forums.

(1) Does Access have some sort of Listbox linking capability like Excel but with the ability to wrap text or use Multiline text for each db row in Access?

If I understand that, "linking" would be akin to the listbox rowsource property. No ability to wrap text as I've said. A datasheet view of a form would do this.

(2) is it possible to export objects, i.e., userforms and buttons to Access with their underlying code and use the same VBA code within Access to continue my programming?

AFAIK, no. Excel userform is a different animal, but then I've never tried. I doubt that it would convert so you'd have to recreate your forms. I suspect a lot of your code is Excel specific as well. Likely you could 'improve' on things if only you knew what options you have. F'rinstance, without knowing exactly what you have my first thought was that one combo can replace 5 buttons from "The East" to "Isaiah". Enter, Find and clear value - 1 combo, maybe with FAYT (find as you type) capability, so several buttons and associated code likely not needed. Anyway, that's just a high level view, possibly clouded by not knowing enough about your app.
HTH.