DATA VALIDATION

[shippey121]

Is there any way either using a setting or macro to increase the amount of lines on the data validation drop down box, what i mean is when i set data validation up for a cell i get the drop down arrow to click, when the drop down appears it shows 8 entries from my list, my list has 23 entries and when displayed it shows the bottom of the list rather than the top, macros are already enabled in this workbook so if it has to be code then that will be fine

 

[Andrew Poulsom]

Look here:

http://www.contextures.com/xlDataVal10.html

 

[shippey121]

thanks, this does exactly what i need

thanks

 

[shippey121]

ive been testing this out and i find that the arrow from the combo box stay visible, the arrow doesnt work unless i double click the cell again,and if i highlight a range of cells it disappears,

is there code where i can refresh / repaint the screen, i could add this to the end of the code im using to make it disappear

the code below is what im using from the link on andrews post, also how can i make it work on all sheets without pasting the code on all sheets

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  a = ActiveCell.Address
  Range("a1:b17").Select
  Range(a).Select
  
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

TIA