Auto width of a listbox on a userform?

M

mctabish

New Member
Joined
Nov 2, 2009
Messages
33
I am trying to create a userform that is reusable by turning on and off diff
objects, and reusing objects
I am running into a little difficulty of resizing the list box for various lengths if items
Example would be if I have a list of items and the longest one is 93 chars long, I need a width of 672
When I have a list that the items are each 5 chars long, I need a listbox width of 92

For simplicity's sake, I am using Courier (supposed to be a monospaced font) pitch is 10. I would have figured that my width
would be simply a matter of finding the longest length in the list and then multiply that times some magical number that
represents the width of the letter (since monospaced, each letter should be the same).

But, with the 93 char long string, the "average length" of each letter seems to be 7.2
(672/93 = ~7.2) but for the shorter words, it seems to be wider at 18 (92/5 = ~18)

Is there a way to have the listbox size itself to the width of the strings?

Thanks
Bruce
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
I tried and got the ole "Compile error: Method or data member not found"
Thanks for the suggestion though! It made sense. But regretibly, it failed.
 
Upvote 0
Bruce

For simplicity's sake, what are you trying to do?

Seems like you are overcomplicated things.

For a start a listbox doesn't have an AutoSize property/method.

I've honestly never seen that used with the other types of control that do support it.:)
 
Upvote 0
I have several lists that I need to have the enduser select from. The list is various lengths as far as itemcount and length of each item. I am not concerned about how high.

I am trying to use the same form instead have having 4 different ones that do the same thing. I thought I could reuse the forms. Functionally, I have done this, but cosmetically, it leaves something to be desired. For example in one "mode" the list box elements are each 93 char. long and it look nice having the WIDE listbox, however, there are 2 modes that the listbox handles much shorter length items (about 5 chars wide in one case and 12 chars wide in the other. It just looks silly to have a REALLY WIDE listbox when a narrow one would work.

I did not think listbox had autosize but when delaneyjm suggested, I thought well it makes sense, and you know how well MS documents all of the properties:rolleyes:.. I thought that it could be there

I know HOW to resize, but I just trying to figure out if there is a way to determine HOW wide.
Again, functionally it works, just looks wierd...:oops:

So this is what I am trying to do.

Thanks


Bruce
 
Upvote 0
Sorry, just moved systems and meant to post here, but don't have all of my FAVORITES set up yet, and went to the wrong site (grey matter messup!)

Sorry...
 
Upvote 0
mctabish said:
...don't have all of my FAVORITES set up yet, and went to the wrong site (grey matter messup!)...
Click to expand...


:LOL: LOL. As one who suffers too many 'blonde moments', I certainly understand:rolleyes:

I'm glad you were able to get a nice solution as well :)

Mark
 
Upvote 0
This technique can also be adapted to autosize the height.
The additional 18 is to account for the possibility of a scroll bar.
Code: 
Private Sub UserForm_Initialize()
    Dim oneCell As Range
    
    Rem textbox properties that could be set a design time
    With TextBox1
        .Width = 10
        .MultiLine = True
        .WordWrap = False
        .Text = vbNullString
        .AutoSize = True
        .Visible = False
    End With

    For Each oneCell In Range("A1:A10").Cells
        ListBox1.AddItem CStr(oneCell)
        With TextBox1
            .Text = .Text & vbCr & CStr(oneCell)
        End With
    Next oneCell
   
    ListBox1.Width = TextBox1.Width + 18
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Changing the Rowsource of a userform Listbox with each change in the Listbox selection
Replies
0
Views
267
chazrab
C
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
452
pure vito
P
M
  • Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
526
Megg
M
S
  • Question
Looping through Listboxes
Replies
1
Views
246
senorcalidad
S
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
532
pure vito
P

Forum statistics

Threads
1,215,313
Messages
6,124,201
Members
449,147
Latest member
sweetkt327

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top