Add DropDownLines

[omnibuster]

In The Sheet I need more Lines in the DropDownContrl.
How this make?
MacroRecorder dont help me!

[GTO]

Greetings,

In your example wb, you have Data Validation using a range of cells as a list, and you also have Forms type drop-down that uses the same range as the Input Range.

As you mention trying the macro recorder, I am guessing that you want to make some type of list dynamic. Do you care whether its thru DV, drop-down, or activex?

Also, what is the criteria that we need to meet to size/repopulate?

Mark

[lucas]

[vba]Option Explicit
Private Sub CommandButton1_Click()

With ActiveSheet.Shapes("my control")
.ListFillRange = "Sheet1!$A$1:$A$499"
.LinkedCell = ""
.DropDownLines = 155
.Display3DShading = False
End With
End Sub[/vba]

see attached.

[omnibuster]

Thanks Lucas, GTO.
That Forms type drop-down was example.

GTO.
Yes.This is Data, Validation, List...
Criteria= Visible part of WorkSheet i.e. ca 155 Listitems.

[vba]
Private Sub CommandButton2_Click()
Dim LastRow As Long
Sheets("Sheet2").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2").Select
With Selection.Validation
' .Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Sheet1!$A:$A"")"
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Sheet1!.Range("A1:A" & LastRow)"")"

.DropDownLines = ActiveSheet.Range("A2").Value ' Or .DropDownLines = 155
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub[/vba]

[Bob Phillips]

I don't believe there is any way to increase the number of lines in DV.