You can add code to test the cell after the combo box loses focus:
'==========================
Private Sub TempCombo_LostFocus()
Dim str As String
Dim wsList As Worksheet
Dim rngLinked As Range
Dim rngList As Range
Set wsList = Sheets("ValidationLists")
Application.EnableEvents = False
Set rngLinked = Range(TempCombo.LinkedCell)
Set rngList = wsList.Range(TempCombo.ListFillRange)
If Application.WorksheetFunction.CountIf(rngList, rngLinked.Value) = 0 Then
MsgBox "Not a valid entry"
'Application.Undo
TempCombo.Value = ""
rngLinked.Value = ""
End If
rngLinked.Activate
If TempCombo.Visible = True Then
With TempCombo
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
Application.EnableEvents = True
End Sub
'=======================
MelissaDeMille wrote:
> OK, as I suspected, I have another question. I have linked the combo box
> using the data valadation feature, and input my information for an error
> message when a value that is not in the list is entered.
>
> I do not get those error messages, and am allowed to enter information that
> is not in the list. I have tested it using just the list, the problem is that
> I would prefer to
> have the autocomlete feature that you get with the combo box. Any help would
> be appreciated.
>
> Thanks again.
>
> here is the coding I am currently using:
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> Cancel As Boolean)
> Dim str As String
> Dim cboTemp As OLEObject
> Dim ws As Worksheet
> Dim wsList As Worksheet
> Set ws = ActiveSheet
> Set wsList = Sheets("2007 Manpower")
>
> Cancel = True
> Set cboTemp = ws.OLEObjects("EmpName")
> 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
> '=========================================
> '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
> '====================================
>
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim str As String
> Dim cboTemp As OLEObject
> Dim ws As Worksheet
> ' added line
> If Application.CutCopyMode <> False Then Exit Sub
> Set ws = ActiveSheet
> Application.EnableEvents = False
> Application.ScreenUpdating = True
>
> Set cboTemp = ws.OLEObjects("EmpName")
> 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 '====================================
>
>
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
Similar Threads
