Link to home
Start Free TrialLog in
Avatar of hindersaliva
hindersalivaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel In-Cell Dropdown is very wide

User generated image
Suddenly my In-cell dropdowns are wider than they were. Why does this happen?
FYI, the dropdown is populated by VBA from a table in Access. Though I suspect this is not the reason.
Sub GetDataToInCellDropdownLeaverReason()

    Dim rsData As ADODB.Recordset
    Dim sSQL As String

    Call DBConnectionAccess

    sSQL = "SELECT LeaverReason FROM tblLeaverReasons ORDER BY LeaverReason"

    'run the query
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    'rsData.Open sSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

    If Not rsData.EOF Then

        Do Until rsData.EOF
            strItems = strItems + rsData.Fields.Item("LeaverReason").Value + ", "
            rsData.MoveNext
        Loop

    End If
    strItems = Left(strItems, Len(strItems) - 1)

    'MsgBox "XXX" + strItems + "XXX"
    
    Call UnprotectSheet
    
    With Range("LeaverReasonInCellDropdown").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=strItems
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Call DBConnectionClose
    
    Call ProtectSheet
   
End Sub
 

Open in new window

SOLUTION
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hindersaliva

ASKER

I think my problem is intermittent. It seems to be ok without any adjustment. I have allocated the points equally.