Solved

List box that can accommodate long sentences

Posted on 2011-03-18
15
772 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
 

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 41

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 41

Assisted Solution

by:dlmille
dlmille earned 500 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 41

Accepted Solution

by:
dlmille earned 500 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 41

Expert Comment

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

Dave
0
 
LVL 41

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 41

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now