XL: Only enter UserForm data if it doesn't already exist in the target 1

[MeGustaXL]

Hello Again! One day I’ll nail this “Select/Activate/Do Neither” issue, I swear!

Thanks to Mike on Thread707-648577 I’ve got all my UserForm data entered in a new row on sheet “Lists” as soon as the “OK” button is pressed.

Trust me to want to tart it up and get stuck again! Now I only want it to enter the data if the value in the first ComboBox doesn’t exist in Column A!

ComboBox1 is called cboSerNo, and is Tagged 1. Column A contains the dynamic Named Range “Serial_No”

The kind of thing I’m after is:

If NOT(cboSerNo.Value EXISTS IN [Serial_No]) then
ENTER THE DATA
Else
HIGHLIGHT THE ROW CONTAINING THE MATCH
LOAD THE FORM CONTROLS WITH THE VALUES FROM THAT ROW
End If

I know how to do this with an array formula in a cell:

={IF(OR($G$4=Serial_No),"Highlight","Not There&quot} will detect if the value entered in $G$4 exists in the range Serial_No and result in “Highlight” if it does, and “Not There” if it ain’t. Can this be done within this data entry code?

‘ Bowers74’s most excellent code
‘
For Each ctrl In Me.Controls
If IsNumeric(ctrl.Tag) Then
i = Val(ctrl.Tag)
Cells(r, i) = ctrl.Value
End If
Next ctrl

Fingers Crossed!

Chris

 

[Bowers74]

Not sure Chris, but I belive that this will work:

If [serial_no].Find(cboSerNo.Value) = Nothing Then
' Enter Data

Try it and let me know how it works out!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Not too good Mike; it gives the error "Invalid use of object" with "Nothing" highlighted.

I changed the syntax to

If .Find(cboSerNo.Value, [Serial_No]) = Nothing Then (as per Help)

and got the same error

I'll try a loop thru' [Serial_No] and set a flag if cboSerNo.Value is found, see if that works.

Chris

 

[Bowers74]

My Bad,

Try this:

If Not IsError([serial_no].Find(cboSerNo.Value)) Then
' Enter data

That should do it!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Hi Mike

Sadly, that still crashed and burned.

The closest I’ve got so far is using Skip’s code from thread707-595304 “Updating a list” which dealt with an almost identical situation.

Private Sub btnOK_Click()
Dim i As Integer, r As Long, s As Variant
Dim ctrl As Control
******************** SKIP’s CODE ****************************

If IsError(Application.Match(cboSerNo.Value, [Serial_No], 0)) Then

*****************************************************************
With Sheets("Lists&quot
[A65536].End(xlUp).Offset(1, 0).Select
r = ActiveCell.Row
For Each ctrl In Me.Controls
If IsNumeric(ctrl.Tag) Then
i = Val(ctrl.Tag)
Cells(r, i) = ctrl.Value
End If
Next ctrl
CopyRow
End With
End If
End Sub

This works, up to a point; If cboSerNo.Value is a string and it exists in [Serial_No], then the record isn’t added
But if cboSerNo.Value is a number like 1000, 3456 etc, and exists in [Serial_No], it goes ahead and adds the record anyway!

I’ve tried the following fixes:

1. Format the Serial_No column as Text……...No change
2. Changed cboSerNo.Value to cboSerNo.Text……..No Change
3. Changed cboSerNo.Value to Val(cboSerNo.Value) ……...Ignores Numbers, inserts Text!

Any Ideas?

Chris

 

[MeGustaXL]

Solved it!!!

If ([serial_no].Find(cboSerNo, , xlValues)) Is Nothing Then
'Enter the record

Does the trick! YEEEHAARR!

Have a star, Buddy, for pointing me at the right method, even if the syntax needed repair!

"Varium et mutabile semper Excel"

Chris

 

[Bowers74]

I knew that it was something like that!

Thanks for the star, anyway!

Glad I could "help".



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!