Controlling a dropdown List Box

EE Pros,

I have a dropdown List Box I use (H, M, L) for "HIGH", "MEDIUM" and "LOW" identification.  When I select the list box it is unusually long with the H, M, and L to the far left.  How do I control the list box so that it fits the small, single Characters instead of going wide on me?

Thank you in advance,

B.
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
What kind of ListBox are you referring to?
Martin LissOlder than dirtCommented:
Can you post your workbook?
Martin LissOlder than dirtCommented:
This workbook assumes that you are using an ActiveX listbox and that you only want the user to see H, M and L but you want to use "HIGH", MEDIUM, and LOW somewhere else. In this workbook the listbox is set up and filled in Workbook_Open.

To test it, select H, M or L.
Q-28670989.xlsm
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Bright01Author Commented:
Absolutely.  Take a look at Cell C4 and when you drop it down you will see the "excess" space in the list box listing H, M or L.

B.
Fixing-List-Box.xlsm
Roy CoxGroup Finance ManagerCommented:
I think it's the Data validation message that you are referring to. I don't think that you can adjust this but you could possible use a comment instead. That can be sized however you wish.
Martin LissOlder than dirtCommented:
Your code is password protected.

I see the yellow box that has H = "HIGH", etc. What do you want to see instead?
Roy CoxGroup Finance ManagerCommented:
See this quick example of what I mean
Fixing-List-Box--1-.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
You can adjust the data validation message. Just go to Data->Data validation and change this

...
Bright01Author Commented:
When you drop down the list box, you will see the HM and L.   The list box will be unusually long. I'm trying to shorten the list box so that it is more conducive to the size and length of the single character text.

B
Roy CoxGroup Finance ManagerCommented:
I'm sure that you are referring to the yellow message box not the actual drop down. I don't know if you can adjust that size, martin might but I have attached an alternative suggestion.
Martin LissOlder than dirtCommented:
Do you just mean that you don't want to see the blank line below "L- LOW"? If so I don't know how to remove it.
Martin LissOlder than dirtCommented:
Roy's is a good workaround. If you don't want to see the little red triangle then do this.

Application.DisplayCommentIndicator = xlNoIndicator
Martin LissOlder than dirtCommented:
I did some experimentation and it seems that the blank line is added when, believe it or not, the width of the text exceeds a certain size. Try these values (there are no spaces) and see if it looks better.

L=LOW
M=MEDIUM
H=HIGH
Bright01Author Commented:
Gentlemen,  What I'm trying to do is to control the "length" of the dropdown list box which I cannot find how to control it anywhere.  You will see when you drop the box a Text message, H=High, M=Medium and L=Low.  No problem with the message box.  It's when you bring the drop down, down, that you will see H, M or L with a really long box and the single character values all the way to the left.  How does it format a dropdown box?  I get different results with different drop downs........

B.
Bright01Author Commented:
Roy,

I just downloaded your example..... YES....that's the fix.  How did you do that?  How did you get H, M and L to be a more manageable size dropdown box?

B.
Martin LissOlder than dirtCommented:
Roy is using a cell comment rather than data validation, so you'll need to copy the comment to all the cells where you want it to appear, and, as I said above, if you don't want to see the little red triangle then do this.

Application.DisplayCommentIndicator = xlNoIndicator
Martin LissOlder than dirtCommented:
Also if you want to stick with data validation then try what I suggested in post 40769335 and see if making the width of the text smaller by leaving out the spaces makes any difference.
Bright01Author Commented:
Got it!  Will "give it a go".  

Quick question for you.  Is there a way to only have the cell comment show up if there is actual text in the cell directly to the right of the cell using the cell comment?  Right now, I'd copy it in all cells regardless of if there is a option to rank or not.

B.
Martin LissOlder than dirtCommented:
Can you tell me the password for your workbook?
Martin LissOlder than dirtCommented:
Add this code to your workbook and remove your Data Validation Input Message.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    
    Range("C2:C" & UsedRange.Rows.Count + 1).ClearComments
    If Not Intersect(Target, Range("C2:C" & UsedRange.Rows.Count + 1)) Is Nothing Then
        If Target.Offset(0, 1) <> "" Then
            Target.AddComment
            Target.Comment.Text Text:="L = LOW" & vbLf & "M = MEDIUM" & vbLf & "H = HIGH"
            Target.Comment.Shape.TextFrame.AutoSize = True
        End If
    End If
 

Open in new window

Martin LissOlder than dirtCommented:
Try this workbook that includes the code I posted above.
Q-28670989-2.xlsm
Rob HensonFinance AnalystCommented:
The width of the List is tied to the width of the cell plus the width of the Drop Down indicator.

Adjust the width of the column to only 1.5 and it will contain the single character but the Selection list still includes the width of the drop down arrow.
Bright01Author Commented:
Thanks guys!  It helped me realize that we have little control over the List Box sizing (except for Rob's workaround).  But do appreciate the quick response and test cases.  I was able to use Rob's workaround to get what I needed.  Martin, thanks, as always for putting it under the microscope.

Best regards to all,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.