conflict in data of columns

[mrxdm]

Hi,
I am a herbal medicine practioner, I made a excel program to help me in my trade. I need some help from excel vba masters that how can I do vba coding for following:

1) Sheet1 col A and B
[TABLE="width: 161"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Medicine[/TD]
[TD]Tmp No[/TD]
[/TR]
[TR]
[TD]Mohrak[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Shadeed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Seelani[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Moqvee[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Taryaq[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Mohrak[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Moshel[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Akseer[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Moqvee[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Taryaq[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jwarish Imli[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Hab Sabir[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mohrak[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Shadeed[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Moshel[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ojaee[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Atreefal[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Moshel[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Akseer[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Molyen[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Hazim[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Foladi[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Soosi[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Taryaq meda[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Akseer Itfaal[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Akseer Jigar[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Hab Mqvee Khas[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Akseer Jadeed[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Moqvee[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Taryaq[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Akseer[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Shadeed[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Molyen Khas[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Hab Shifa[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
(It is a long list but I just paste a short sample.)

What I need is:
there are 8 Combo boxes (all populated with the col A sheet1, and a label.caption with combo box showing it Tmp No according to selection) on my Userform to select the medicine for a patient, but some medicines should not be mixed with each other according to there Tmp Numbers, for example:

Tmp No 1 can not be mixed with Tmp No 2 and 3.
Tmp No 2 can not be mixed with Tmp No 1, 5 and 6.
Tmp No 3 can not be mixed with Tmp No 6 and 1.
Tmp No 5 can not be mixed with Tmp No 3.
Tmp No 6 can not be mixed with Tmp No 2 and 4

If I select medicine of two different Temperaments from any comboboxes in userform then warning alert message show an alert "Do not mix these medicines".

Please help in this matter to solve my problem.

Thanks

 

[My Aswer Is This]

Are you saying you have a label for all 8 comboboxe's

So if David is in column A and David is selected in combobox1 and Smith is in column B next to David
Then you want label1 caption to say Smith

Is this correct?

And after selecting all the values in all 8 comboboxes and all the labels do not say Smith then have a label with a warning saying

Do not mix these medicines​

.

 

[MickG]

Try this, based on Numbers in individual labels relating to individual ComboBoxes.
The code expects "Tmp" numbers to be inserted in Related Label Caption as selections from Comboboxes are made.
The code is run from a "CommanButton" on Userform.

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
'[COLOR="Green"][B] Tmp No 1 can not be mixed with Tmp No 2 and 3.[/B][/COLOR]
'[COLOR="Green"][B] Tmp No 2 can not be mixed with Tmp No 1, 5 and 6.[/B][/COLOR]
'[COLOR="Green"][B] Tmp No 3 can not be mixed with Tmp No 6 and 1.[/B][/COLOR]
'[COLOR="Green"][B] Tmp No 5 can not be mixed with Tmp No 3.[/B][/COLOR]
'[COLOR="Green"][B] Tmp No 6 can not be mixed with Tmp No 2 and 4[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Ctrl [COLOR="Navy"]As[/COLOR] Control, R [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean, Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ray = Array(Array(1, 2, 3), Array(1, 2, 5, 6), Array(1, 3, 6), Array(3, 5), Array(2, 4, 6))
 nStr = ""
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ctrl [COLOR="Navy"]In[/COLOR] Me.Controls
 [COLOR="Navy"]If[/COLOR] TypeName(Ctrl) = "Label" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(Ctrl.Caption) [COLOR="Navy"]Then[/COLOR]
        nStr = nStr & IIf(nStr = "", Ctrl.Caption, ", " & Ctrl.Caption)
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ctrl
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Ray
    Fd = False
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(R)
        [COLOR="Navy"]If[/COLOR] InStr(nStr, R(n)) > 0 [COLOR="Navy"]Then[/COLOR]
            Fd = True
        [COLOR="Navy"]Else[/COLOR]
            Fd = False
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
    Msg = Msg & vbLf & Join(R, ",")
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]If[/COLOR] Not Msg = "" [COLOR="Navy"]Then[/COLOR]
    MsgBox "The following Conflicts occurred :-" & vbLf & Msg
[COLOR="Navy"]Else[/COLOR]
    MsgBox "No Conflict Found"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mrxdm]

Thanks a lot Mick that you replied to my problem, I saw it today and let me try it in my program than I shall reply you with the " results of this code ".

I appreciate your efforts.

Hkm. Riaz