0

I want add to created DataValidation DropDownList two additional things. First is to set number of rows in DropDownList to specific number, like it is possible to do with normal combolist in VBA by ".ListRows = 11". Second is to autocomplete text typed by user (MatchEntry in combolist) to the cell based positions from DropDownList if he start typing it manually instead of choosing from list. I see that I cannot do it from position in Validation.Add Method (Excel) and I do not know enough about code construction how can I do it, or how can I referee to created list by VBA code. 

With Range("F26:H26").Validation
    .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=RootCauses!A1:A11"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = varValTitle
    .ErrorTitle = varValErrTitle
    .InputMessage = varValMessage
    .ErrorMessage = varValErrMessgae
    .ShowInput = True
    .ShowError = True
    .ListRows = 11 '← my first broken solution
    .MatchEntry = 1 '← my second broken solution
End With
NoOne
  • 33
  • 1
  • 7
  • No need to limit rows. This section of your code already does that `Formula1:="=RootCauses!A1:A11`. Data validation does not support auto-complete. Because Excel does not expose a key press event it would be very hard to build your own (if you are comfortable using the WinAPI [there is a workaround](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell) - but it's advanced stuff). – David Rushton Aug 29 '17 at 13:45
  • Thanks, but it have 8 rows, I need 11 to show all possibilities in one moment without scrolling – NoOne Aug 29 '17 at 13:50
  • Sorry more bad news. Excel doesn't include an option to customise the max number of rows shown. – David Rushton Aug 29 '17 at 14:20

1 Answers1

0

Ok, so Data Validation have dropdown list as an additional option, So I create combolist object and set all parameters to autocomplete typing words, set number of rows and fit to the required range.

NoOne
  • 33
  • 1
  • 7