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
Its a little more complicated then that but this is just a relative idea of what im trying to do.
Please Login or Register to view this content.
Thanks in advance...
Chris
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks