Combobox Query

[Greg]

Hi all

I have 4 comboboxes on my userform and they are called c1 c2 c3 and c4 I
want to have excel check up to make sure the same value is not used twice.
I have tries this myself and have not suceeded because of when it is left
blank it activates the code i have written to warn of duplicates.

Can some one help me with this

Thanks

Greg

[C01d]

I assume that your code does not give special treatment to blank cells. When left blank, your code detects two empty strings and finds that they are the same. Hence it executes the code for duplicates.

If you want to disallow blank inputs, simply check for that first and exit from the code if any blank inputs are found.

If you want to check for duplicates while ignoring blank cells you can use the following condition when comparing two values:

if(c1.value<>"" and c2.value<>"" and c1.value=c2.value) then
execute code for duplicates
endif

[cush]

This may help you get started. The code goes in the Userform module

Option Explicit

Private Sub c1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If c1 <> "" Then
If c1 = c2.Value _
Or c1 = c3.Value _
Or c1 = c4.Value Then
MsgBox "That value is already used."
Else
MsgBox "Okay"
''Do something here - your code
End If
End If
End Sub

"Greg" wrote:

> Hi all
>
> I have 4 comboboxes on my userform and they are called c1 c2 c3 and c4 I
> want to have excel check up to make sure the same value is not used twice.
> I have tries this myself and have not suceeded because of when it is left
> blank it activates the code i have written to warn of duplicates.
>
> Can some one help me with this
>
> Thanks
>
> Greg
>
>
>

[Greg]

Thanks for that

Greg
"C01d" <[email protected]> wrote in message
news:[email protected]...
>
> I assume that your code does not give special treatment to blank cells.
> When left blank, your code detects two empty strings and finds that
> they are the same. Hence it executes the code for duplicates.
>
> If you want to disallow blank inputs, simply check for that first and
> exit from the code if any blank inputs are found.
>
> If you want to check for duplicates while ignoring blank cells you can
> use the following condition when comparing two values:
>
> if(c1.value<>"" and c2.value<>"" and c1.value=c2.value) then
> execute code for duplicates
> endif
>
>
> --
> C01d
> ------------------------------------------------------------------------
> C01d's Profile:
> http://www.excelforum.com/member.php...o&userid=34422
> View this thread: http://www.excelforum.com/showthread...hreadid=544149
>