I'm wondering how I can Check for duplicates in a listbox. The listbo
is filled with values from a combobox and has numbers 1000 -8000.
need to say ok, You've already entered a value for 1000 please chec
the posting code and try again.


I was trying something like


Code
-------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end wit
-------------------


Its a little more complicated then that but this is just a relativ
idea of what im trying to do.

Thanks in advance...
Chris :cool

--
justchri
-----------------------------------------------------------------------
justchris's Profile: http://www.excelforum.com/member.php...nfo&userid=889
View this thread: http://www.excelforum.com/showthread.php?threadid=40139

You either have to loop through every item checking to see whether it is
used in the combo, or if the combo is linked to a worksheet range, you could
use MATCH to check it

If Not Iserror(application.match(userform2.listbox1.value , range("A1:A20"))
then
msgbox "im sorry you've entered duplicate charges"
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"justchris" wrote
in message ...

I'm wondering how I can Check for duplicates in a listbox. The listbox
is filled with values from a combobox and has numbers 1000 -8000. I
need to say ok, You've already entered a value for 1000 please check
the posting code and try again.


I was trying something like


Code:
--------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end with
--------------------


Its a little more complicated then that but this is just a relative
idea of what im trying to do.

Thanks in advance...
Chris


--
justchris
------------------------------------------------------------------------
justchris's Profile:
http://www.excelforum.com/member.php...fo&userid=8894
View this thread: http://www.excelforum.com/showthread...hreadid=401393

I like to use a scriting dictionayr for this. set a refetence to MS scripting
runtime.
A dictionary is like a collection, but has more features and especially an
Exists method that is useful for checking if a 'key'already exists.

so
DIM dic as Scripting.Dictionary

SET dic = New Scripting.Dictionary

loading the listbox...

for eact item in loadlist
IF NOT dic.Exists(item) THEN
dic.Add item, item
listbox1.Add item
END IF
Next

the same check can be used to check when a user tries to ad an item...

IF dic.Exists(item) THEN
msgbox "This item already exists in the list",,item
ELSE
dic.Add item, item
listbox1.Add item
END IF

HTH






"justchris" wrote:


I'm wondering how I can Check for duplicates in a listbox. The listbox
is filled with values from a combobox and has numbers 1000 -8000. I
need to say ok, You've already entered a value for 1000 please check
the posting code and try again.


I was trying something like


Code:
--------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end with
--------------------


Its a little more complicated then that but this is just a relative
idea of what im trying to do.

Thanks in advance...
Chris


--
justchris
------------------------------------------------------------------------
justchris's Profile: http://www.excelforum.com/member.php...fo&userid=8894
View this thread: http://www.excelforum.com/showthread...hreadid=401393

Patrick,

An IfExists function for a collection


'--------------------------------------------------------------------------
Public Function ExistsInCollection(col As Collection, ByVal sKey As String)
'--------------------------------------------------------------------------
On Error Goto NoSuchKey
If VarType(col.Item(sKey)) = vbObject Then
' force an error condition if key does not exist
End If
ExistsInCollection = True
Exit Function

NoSuchKey:
ExistsInCollection = False
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Molloy" wrote in message
...
I like to use a scriting dictionayr for this. set a refetence to MS
scripting
runtime.
A dictionary is like a collection, but has more features and especially an
Exists method that is useful for checking if a 'key'already exists.

so
DIM dic as Scripting.Dictionary

SET dic = New Scripting.Dictionary

loading the listbox...

for eact item in loadlist
IF NOT dic.Exists(item) THEN
dic.Add item, item
listbox1.Add item
END IF
Next

the same check can be used to check when a user tries to ad an item...

IF dic.Exists(item) THEN
msgbox "This item already exists in the list",,item
ELSE
dic.Add item, item
listbox1.Add item
END IF

HTH






"justchris" wrote:


I'm wondering how I can Check for duplicates in a listbox. The listbox
is filled with values from a combobox and has numbers 1000 -8000. I
need to say ok, You've already entered a value for 1000 please check
the posting code and try again.


I was trying something like


Code:
--------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end with
--------------------


Its a little more complicated then that but this is just a relative
idea of what im trying to do.

Thanks in advance...
Chris


--
justchris
------------------------------------------------------------------------
justchris's Profile:
http://www.excelforum.com/member.php...fo&userid=8894
View this thread:
http://www.excelforum.com/showthread...hreadid=401393

great. thanks Bob

"Bob Phillips" wrote:

Patrick,

An IfExists function for a collection


'--------------------------------------------------------------------------
Public Function ExistsInCollection(col As Collection, ByVal sKey As String)
'--------------------------------------------------------------------------
On Error Goto NoSuchKey
If VarType(col.Item(sKey)) = vbObject Then
' force an error condition if key does not exist
End If
ExistsInCollection = True
Exit Function

NoSuchKey:
ExistsInCollection = False
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patrick Molloy" wrote in message
...
I like to use a scriting dictionayr for this. set a refetence to MS
scripting
runtime.
A dictionary is like a collection, but has more features and especially an
Exists method that is useful for checking if a 'key'already exists.

so
DIM dic as Scripting.Dictionary

SET dic = New Scripting.Dictionary

loading the listbox...

for eact item in loadlist
IF NOT dic.Exists(item) THEN
dic.Add item, item
listbox1.Add item
END IF
Next

the same check can be used to check when a user tries to ad an item...

IF dic.Exists(item) THEN
msgbox "This item already exists in the list",,item
ELSE
dic.Add item, item
listbox1.Add item
END IF

HTH






"justchris" wrote:


I'm wondering how I can Check for duplicates in a listbox. The listbox
is filled with values from a combobox and has numbers 1000 -8000. I
need to say ok, You've already entered a value for 1000 please check
the posting code and try again.


I was trying something like


Code:
--------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end with
--------------------


Its a little more complicated then that but this is just a relative
idea of what im trying to do.

Thanks in advance...
Chris


--
justchris
------------------------------------------------------------------------
justchris's Profile:
http://www.excelforum.com/member.php...fo&userid=8894
View this thread:
http://www.excelforum.com/showthread...hreadid=401393

A dropped 0?

If Not Iserror(application.match(userform2.listbox1.value , range("A1:A20"), 0)




Bob Phillips wrote:

You either have to loop through every item checking to see whether it is
used in the combo, or if the combo is linked to a worksheet range, you could
use MATCH to check it

If Not Iserror(application.match(userform2.listbox1.value , range("A1:A20"))
then
msgbox "im sorry you've entered duplicate charges"
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"justchris" wrote
in message ...

I'm wondering how I can Check for duplicates in a listbox. The listbox
is filled with values from a combobox and has numbers 1000 -8000. I
need to say ok, You've already entered a value for 1000 please check
the posting code and try again.


I was trying something like


Code:
--------------------
if userform2.listbox1.value = userform2.combobox1.value then
msgbox "im sorry you've entered duplicate charges"
else
with userform2.listbox1
.additem userform2.combobox1.value
end with
--------------------


Its a little more complicated then that but this is just a relative
idea of what im trying to do.

Thanks in advance...
Chris


--
justchris
------------------------------------------------------------------------
justchris's Profile:
http://www.excelforum.com/member.php...fo&userid=8894
View this thread: http://www.excelforum.com/showthread...hreadid=401393


--

Dave Peterson