Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Excel Dropdown - Variable width Spacing


  • Please log in to reply
2 replies to this topic
jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

item1               01
this item          02
another one    03
item 4               04
end                   05

 

Anyone know of a way to create proper spacing between strings in an Excel Dropdown without simply using a predetermined number of spaces? For example, in the above quote the second column (01,02, etc) is approx lined up horizally. Of course different fonts & characters make using the Len() function inadequate. I was thinking there might be a way to measure the text, but I wouldn't want to get too complex, nor would I wan't to have the width measured after display - as in using gdip.

 

One idea I had was to put the each item in the first column in separate columns - autofit - & then use the column width. I was wondering if anyone had any better ideas.



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012
hmm not sure but maybe you can use a combobox with columns

Not my Code
Private Sub UserForm_Initialize()
    Dim rSource As Range
    
    With Sheets("Sheet1")
        Set rSource = .Range(.Cells(2, 1), Cells(.Rows.Count, 1).End(xlUp)).Resize(, 3)
    End With
    
    With Me.ComboBox1
        .ColumnCount = 3
        .List = rSource.Value
    End With

End Sub
hope it helps
Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

Thanks - though your example assumes using an ActiveX ComboBox control, rather than a simple Data Validation dropdown. Since the ListFillRange property (Excel 2010) can be set to a range with 2 columns, this would work quite well for the display I asked about. However, the ComboBox then does not act like a regular cell - for tabbing, etc. Here are a few Subs that help mimic proper tabbing & display of the ComboBox text, assuming the ComboBox is over/in Cell C2 (and is named 'ComboBoxC2'):

Private Sub ComboBoxC2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Set Cell = Range("C2")
    If KeyCode = 9 Or KeyCode = 13 Then
        If Shift Then
            Cell.Offset(-1, 0).Activate
        Else
            Cell.Offset(1, 0).Activate
        End If
    End If
End Sub

Private Sub ComboBoxC2_LostFocus()
    If InStr(1, ComboBoxC2.Text, vbTab) = 0 Then
        ComboBoxC2.Text = ComboBoxC2.Text & vbTab & ComboBoxC2.Value
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "C2"
            With Me.OLEObjects("ComboBox" & Target.Address(0, 0))
                .Activate
                .Object.SelStart = 0
                .Object.SelLength = Len(.Object.Text)
            End With
    End Select
End Sub

Note - the TextColumn property is set to column 1, which would be the text before the spaces in my original post.