MS Forms 2.0 combobox cuts off text

totalchaos

New Member
Joined
Jul 6, 2012
Messages
40
I have this code in the initialize event of a userform in excel vba:
Private Sub UserForm_Initialize()
Dim ws As Worksheet, iCol As Integer, iRow As Integer, _
a As Integer, b As Integer
Set ws = ActiveWorkbook.Worksheets("Sheet1")
iCol = 1
iRow = 20
a = iRow
For a = 2 To iRow
cbPeriodEnd.AddItem CStr(ws.Cells(a, iCol))
Next a
cbPeriodEnd.Text = cbPeriodEnd.List(0, 0) 'first item
End Sub

In range A1:A20 of Sheet1 I have:
PeriodEnd
2/26/2012
3/4/2012
3/11/2012
3/18/2012
3/25/2012
4/1/2012
4/8/2012
4/15/2012
4/22/2012
4/29/2012
5/6/2012
5/13/2012
5/20/2012
5/27/2012
6/3/2012
6/10/2012
6/17/2012
6/24/2012
7/1/2012

When I run the code, the dropdown box cuts off the last two digits of each date. The textalign property for the
combobox is frmtextalignright. All other properties are default. Can anyone help me fix this? I'm sure it's something
simple I'm overlooking.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Try something like this...

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()


    [color=darkblue]With[/color] cbPeriodEnd
        .List = Sheets("Sheet1").Range("A2:A20").Value [color=green]'Populate the list[/color]
        .ListIndex = 0 [color=green]' select the first item[/color]
        
        [color=green]'***Do one of these two things****[/color]
        .ListRows = .ListCount + 1  [color=green]'Remove the vert scroll bar that hides the last 2 digits[/color]
        [color=green]'or[/color]
[color=green]'        .TextAlign = fmTextAlignLeft    'Align the list to the left[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

totalchaos

New Member
Joined
Jul 6, 2012
Messages
40
Try something like this...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()


    [COLOR=darkblue]With[/COLOR] cbPeriodEnd
        .List = Sheets("Sheet1").Range("A2:A20").Value [COLOR=green]'Populate the list[/COLOR]
        .ListIndex = 0 [COLOR=green]' select the first item[/COLOR]
        
        [COLOR=green]'***Do one of these two things****[/COLOR]
        .ListRows = .ListCount + 1  [COLOR=green]'Remove the vert scroll bar that hides the last 2 digits[/COLOR]
        [COLOR=green]'or[/COLOR]
[COLOR=green]'        .TextAlign = fmTextAlignLeft    'Align the list to the left[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thanks for your reply. My post was a simplified example of something more extensive I'm working on. What I'm ultimately trying to accomplish is to populate the list with an inversion of the range (i.e. the value at the bottom appears first in the list). The range will be dynamic with the latest period end added after the last row of the range. The only way I can think of doing this is using the additem method but I keep getting the problem with text cut off. Not sure why.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Isn't your text cutoff because you aligned the text to the right and the vertical scroll bar hides the last two digits. If yes, then just align the text to the left. If no, then I don't understand the problem.
 

totalchaos

New Member
Joined
Jul 6, 2012
Messages
40

ADVERTISEMENT

Try something like this...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()


    [COLOR=darkblue]With[/COLOR] cbPeriodEnd
        .List = Sheets("Sheet1").Range("A2:A20").Value [COLOR=green]'Populate the list[/COLOR]
        .ListIndex = 0 [COLOR=green]' select the first item[/COLOR]
        
        [COLOR=green]'***Do one of these two things****[/COLOR]
        .ListRows = .ListCount + 1  [COLOR=green]'Remove the vert scroll bar that hides the last 2 digits[/COLOR]
        [COLOR=green]'or[/COLOR]
[COLOR=green]'        .TextAlign = fmTextAlignLeft    'Align the list to the left[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thanks for your reply. My post was a simplified example of something more extensive I'm working on. What I'm ultimately trying to accomplish is to populate the list with an inversion of the range (i.e. the value at the bottom appears first in the list). The range will be dynamic with the latest period end added after the last row of the range. The only way I can think of doing this is using the additem method but I keep getting the problem with text cut off. Not sure why.
 

totalchaos

New Member
Joined
Jul 6, 2012
Messages
40
If I populate a simple one column combo box, it usually shows up with the scroll bar but the full text is visible even if I right align. I'd prefer to align to the right.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
If I populate a simple one column combo box, it usually shows up with the scroll bar but the full text is visible even if I right align. I'd prefer to align to the right.

I can't align text to the right in a simple one column combo box without hiding some text behind the scrollbar.

Below uses a two column combobox with the second column empty. It's used just to take up the space hidden by the scrollbar so the first column is fully visible. You may have to tweak the red values to get it to align properly for your combobox.

Code:
Private Sub UserForm_Initialize()

    Dim Lastrow As Long, i As Long

    With cbPeriodEnd
    
        .ColumnCount = 2
        .ColumnWidths = .Width - [COLOR=#ff0000]17[/COLOR] & ";[COLOR=#ff0000]15[/COLOR]" 'Second column in the list is a spacer
    
        Lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        
        For i = Lastrow To 2 Step -1    'Populate from the bottom up
             .AddItem Sheets("Sheet1").Cells(i, "A").Value
        Next i
         .ListIndex = 0 ' select the first item

    End With
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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
Top