Newbie VBA - Combobox check if value exist in named range, error if not

[Barsoe]

hi

I am new here and have only recently started looking at VBA.
I copied code from various threads to make the function I am looking for... almost there now, but just need a little check to avoid entering invalid data

I have a userform with a combobox, 3 textboxes.

What I need is the value I enter in the combobox (cboVare) to check against the items listed in the range (Varenr).
If it doesn't exist in the range, it should not be possible to add the data to my worksheet.




Code as follows:



Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long

Dim ws As Worksheet
Set ws = Worksheets("Bevægelser")

lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboVare.ListIndex

With ws.Cells(lRow, 4)
.NumberFormat = "dd/mm/yyyy"
.Value = Dato
End With

With ws
.Cells(lRow, 1).Value = Me.cboVare.Value
.Cells(lRow, 5).Value = Me.cboVare.List(lPart, 1)
.Cells(lRow, 2).Value = Me.Ind.Value
.Cells(lRow, 3).Value = Me.Ud.Value
End With

Me.Dato.Value = "dd/mm"
Me.Ind.Value = ""
Me.Ud.Value = ""
cboVare.SetFocus

End Sub


Private Sub UserForm_Initialize()

Dim cVare As Range
Dim ws As Worksheet

Set ws = Worksheets("Varer")

For Each cVare In ws.Range("Varenr")
With Me.cboVare
.AddItem cVare.Value
.List(.ListCount - 1, 1) = cVare.Offset(0, 1).Value
End With
Next cVare


Me.Dato.Value = "dd/mm"
Me.Ind.Value = ""
Me.Ud.Value = ""
Me.cboVare.SetFocus

End Sub



Help appreciated!
thanks

 

[Micron]

I think you're saying that you don't want to allow anyone to type in a value. I say that because it appears your list comes from said range, thus the only list values you should have are the ones you have in the range. If so, see

How to get combobox not to accept user input in Excel-Vba?

Does anyone know what the properties are in the combobox that I can manipulate in order not to allow the user to key/type in any data?

stackoverflow.com