Data validation box width

[ogo]

Hi all,

I am not able to view the full length of data, in a data validation drop down, because the string length is large. Is it possible to change the width of dropdown list when the particular cell is selected? Or is there any work around to this?

Thanks,

Ogo

 

[ogo]

I found something like this

'To change width of validation list
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const ValidWidth = 2 'Change here to change the width of the Data Validation list
    If Target.Row = iSIN Or Target.Row = iSON Then MakeValidationWidthWide Target, ValidWidth
End Sub
Sub MakeValidationWidthWide(ByVal Target As Range, RelativeToOriginalSize)
    Dim wks As Worksheet
    Dim elmDic As Object
    Dim elmShp As Shape
    Dim drpShp As Shape
    Dim objDic As Object
    Set wks = Target.Parent
    On Error GoTo Terminate
    'When the AutoFilter is used in the worksheet
    'this procedure fails, so turn off the AutoFilter
    wks.AutoFilterMode = False
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Validation.Type = xlValidateList Then
        Set objDic = CreateObject("Scripting.Dictionary")
        For Each elmDic In wks.DrawingObjects
            objDic.Add elmDic.Name, elmDic.Name
        Next
        For Each elmShp In wks.Shapes
            If elmShp.Name Like "Drop Down *" Then
                If Not objDic.Exists(elmShp.Name) Then
                    Set drpShp = elmShp
                    Exit For
                End If
            End If
        Next
        If Not drpShp Is Nothing Then
            drpShp.ScaleWidth RelativeToOriginalSize, False, msoScaleFromBottomRight
            SendKeys "%{down}"
        End If
    End If
Terminate:
    Set drpShp = Nothing
    Set objDic = Nothing
End Sub

Thanks,

Ogo