List box that can accommodate long sentences

Hi,

How can I create a list box in Ms-Excel 2003 where I can tye in more words(long sentence)?

The current list box that can be created only accepts a limited amount of words.

Any assistance with this will be highly appreciated.

Thank you.

Smruti Khetia
Smruti123Asked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Yes - on Sheet two, you'd just enable word wrap, correct?  and if you set the linked cell of the listbox to the appropriate place on Sheet 2 it looks like you'll get what you desire.  Don't think you'd want conflicting data captures on two separate sheets - believe that sheet 2 is more of a report than a selection, correct?

having a listbox as a report-out mechanism on sheet2 is do-able, but could make your worksheet unwieldy.  However, I've added that in the attached example as well.

Hit Alt-F11 to get into the VBA side of this and look at the code in Sheet2 and see how it updates.  You should see this:
Private Sub Worksheet_Activate()
    Label1.Caption = Sheets(1).ListBox1.Value
    ListBox1.Clear
    ListBox1.AddItem Sheets(1).ListBox1.Value
End Sub

Open in new window

The first statement updates a label (no scrollbar capability, so probably not an option)
The second clears the listbox, then third statement updates it with what was selected on the prior sheet.

You can see this can create alot of overhead for your worksheet, if you want to do this for each and every cell in Sheet 2.  I would propose that you setup Sheet 2 to have enough cell HEIGHT and with WORD WRAP turned ON to display the lion's share of what was selected in the listbox on sheet 1.

Please see attached...

Cheers,

Dave


See attached
listbox-compromize-with-h-scroll.xlsm
0
 
jppintoCommented:
I don't believe that the purpose of a list box is to have long sentences, that's why it has a limited capacity of words in Excel.
0
 
Smruti123Author Commented:
Thanks for your response jppinto.

Well, would there be another way probably with macros and drop-down menus where this can be done?

Thanks once again.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jppintoCommented:
You have to thinks in terms of U.I. (user interface), it's not the best UI to have a listbox, where you select one item, that has long sentences. What would be the width of that listbox?!? If you put it on a sheet or on a userform, what would need to be the width of the userform to acomodate that large listbox? That's one of the reasons why the lisbox can't have such long items...
0
 
Smruti123Author Commented:
The width of the list box would be to accomodate 50 characters and whatever text comes after that should be wrapped so that it goes to the next line. Find attached a jpg file for your reference.
untitled.JPG
0
 
jppintoCommented:
You canĀ“t wrap text on a listbox on Excel.
0
 
dlmilleCommented:
You may not be able to wrap text with a listbox, but you can set it up such that a horizontal scrollbar appears, and then the user can read the entire entry.

Just set the column widths property to a size that's greater than the size of the listbox, and the horizontal scrollbar will appear automatically.

see picture and attached file demonstrating this.

Cheers,

Dave
listbox-compromize-with-h-scroll.xlsx
0
 
dlmilleConnect With a Mentor Commented:
You may not be able to wrap text with a listbox, but you can set it up such that a horizontal scrollbar appears, and then the user can read the entire entry.

Just set the column widths property to a size that's greater than the size of the listbox, and the horizontal scrollbar will appear automatically.

see picture and attached file demonstrating this.
demo of listbox active-x control with horizontal scrollbarCheers,

Dave
listbox-compromize-with-h-scroll.xlsx
0
 
Smruti123Author Commented:
Thankyou so much dlmille!

Thats exactly what I wanted. However, once I have selected the item on a listbox on Sheet1, is there a way this information can reflect on Sheet2 on a cell or another listbox with the same options avaialable?
Find a printscreen of the two sheets.

Thanks to jppinto for all your suggestions too.

Cheers!

Smruti
ps.jpg
0
 
Smruti123Author Commented:
Thank you very much dlmille! :)

It all worked the way I wanted the data to display on the worksheets.

Thank you for your quick response on this. You're surely a genius.
0
 
dlmilleCommented:
So - how did you end up implementing this?  putting a bunch of listboxes in?

Dave
0
 
dlmilleCommented:
I've been messing around with dynamic objects that appear when you do cell selection (aka listbox/combobox).  I think you can have a solution where when you click on a cell you get that word-wrapping listbox, as opposed to word-wrapped text....

FYI anyway.

Dave
0
 
Smruti123Author Commented:
Hi dlmille,

Yes I placed a bunch of list boxes.

Could you please share how this can be done i.e. when you click on a cell you get that word-wrapping listbox, as opposed to word wrapped text...

Thanks in advance,
Smruti
0
 
dlmilleCommented:
Create a related question to this one - see the "Add Your Comment" box, below - there should be a link, like "You can also ask a related question."  Put the question in there and provide a very small sample.

Dave
0
 
Smruti123Author Commented:
I just did.

Thanks.
0
All Courses

From novice to tech pro — start learning today.