Help for a clueless newcomer

[Ogunhe]

Hello

I have a bunch of useful xl files for an ongoing event i help to manage. It basically enables us to count contributions, deduct expenses, and so on.

However, I would like to link this to a database of members, and the first step, once the membership list is completed, is to be able to list which members are going to be present at any given event.

For this, I would like to be able to begin typing the first few letters of a members name, and the rest autocomplete, or provide a drop down with possibilities.

After a bit of searching I found the Data validation functions, and ActiveX combobox possibility, and this seems to be just what i want.

However, copying the code I have found so that when clicking in a data validation cell the combobox appears, and autocompletes is all going well apart from one thing : the data validation is not really working.

Here is the code I am using:

'==========================
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
Set wsList = Sheets("Members")

Cancel = True
Set cboTemp = ws.OLEObjects("Bookings")
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("Bookings")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub Bookings_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
'====================================


This works perfectly well, except that if I enter a name that doesn't exist on the database, it doesn't object or let me know, which is surely the point of the data validation??

What am I doing wrong? Can anyone point out the mistake in the code?? Many thanks for any help

Dan

 

[Ogunhe]

btw i have a feeling it is to do with the .ListFillRange variable, but have no clue about vba at all, I copied this code from the web and changed the relevant titles of combobox and sheet etc.

many thx for any help

d