MS Forms 2.0 combobox cuts off text

[totalchaos]

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.

 

[AlphaFrog]

Try something like this...

[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]

Try something like this...

[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.

 

[AlphaFrog]

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]

Try something like this...

[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]

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]

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.

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

 

[totalchaos]

Thank you. That works for me. I appreciate your help.