Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

List box that can accommodate long sentences

Posted on 2011-03-18
15
Medium Priority
?
833 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Smruti123
  • 6
  • 6
  • 3
15 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35167341
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
 

Author Comment

by:Smruti123
ID: 35167397
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35167416
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Smruti123
ID: 35167514
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35167545
You can´t wrap text on a listbox on Excel.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35174946
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
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 2000 total points
ID: 35174949
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
 

Author Comment

by:Smruti123
ID: 35183379
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
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35184070
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
 

Author Closing Comment

by:Smruti123
ID: 35189800
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35405921
So - how did you end up implementing this?  putting a bunch of listboxes in?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35405928
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
 

Author Comment

by:Smruti123
ID: 35417251
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35417369
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
 

Author Comment

by:Smruti123
ID: 35417500
I just did.

Thanks.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question