Thread: Code to allow autofill and Validation

Basically i'm building a database in access with all the data handling done across a network in excel.(both 2007) Current problem is validation.

The plan is to have code that takes a validated cell i.e. one that has a list associated with it from the excel built in validation tool and populate a temp combo box with that list, this means that every time a cell is selected a validated list can be chosen from, as well as this as the text is typed in the list is autocompleted!..

i had this working for one sheet, but when i try to create a new instance of it in a new worksheet it fails..

can anyone tell me why?

thanks

Jonathan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


If Target.count > 1 Then GoTo exitHandler

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

  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 + 15
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errhandler:
  Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub

update..

turns out all that needs to be done was renaming a combo box "TempCombo".. not a clue how i missed this.. wasted the better part of a day.. anyway, feel free to use the code if it's helpful fo you, originally from http://www.contextures.com/

Hi Jonathan

I tried to use this code two years ago and it kept locking up Excel (both 2000 and 2007). I'm glad to hear you got it working. I may have to revisit my efforts.

John