Compare two comboboxes in Userform to check selected data can be selected or can not selected...

[mrxdm]

I have a problem as follows,
in my medicine prescription userform I have 4 combobaxes containing medicine names,
combobox1 is populate with col A in sheet 1,
combobox2 is populate with col B in Sheet 1,
combobox3 is populate with col C in sheet 1 and
combobox4 is populate with col D in sheet 1.

at sheet1:
in Col A data of medicine entered which cannot be mixed with Col B.
in Col B data of medicine entered which cannot be mixed with Col C.
in Col C data of medicine entered which cannot be mixed with Col D.
but
in Col D data of medicine entered which can be mixed with Col A.
in Col B data of medicine entered which can be mixed with Col D.

How it is coded in vba that:
If I select two opposite medicine from comboboxes it warns me with a msg: with names of both medicine selected from two comboboxes and say "you can not select two different temperament medicines, please select similar supporting medicine."

I wish that any excel master can solve this problem...

Hk. Riaz Hussain

 

[MickG]

Place this code in your Userfom Module, it will detect all conflicts and warn of conflicts between "ColA/ColB","ColB/ColC","ColC/ColD" and also if no "Conflict" exists"

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
Conflict
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox2_Change()
Conflict
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
Conflict
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox4_Change()
Conflict
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Conflict()
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Com [COLOR="Navy"]As[/COLOR] Variant, Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nMsg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray(1 To 6)
Com = Array(ComboBox1, ComboBox2, ComboBox3, ComboBox4)
[COLOR="Navy"]For[/COLOR] a = 0 To 3
 [COLOR="Navy"]For[/COLOR] b = a + 1 To 3
  [COLOR="Navy"]If[/COLOR] Com(a).Value <> "" And Com(b) <> "" [COLOR="Navy"]Then[/COLOR]
    c = c + 1
    Ray(c) = Com(a).Name & Com(b).Name
   [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] b
[COLOR="Navy"]Next[/COLOR] a
[COLOR="Navy"]For[/COLOR] n = 1 To c
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
        [COLOR="Navy"]Case[/COLOR] Ray(n) = "ComboBox1ComboBox2": Msg = "Conflict CBox1_Cbox2"
        [COLOR="Navy"]Case[/COLOR] Ray(n) = "ComboBox2ComboBox3": Msg = "Conflict CBox2_Cbox3"
        [COLOR="Navy"]Case[/COLOR] Ray(n) = "ComboBox3ComboBox4": Msg = "Conflict CBox3_Cbox4"
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]If[/COLOR] Msg <> "" [COLOR="Navy"]Then[/COLOR]
    nMsg = nMsg & Msg & vbLf
    Msg = ""
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
MsgBox IIf(nMsg = "", "No Conflict", nMsg)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mrxdm]

Thanks a lot Mick for your attention on my problem, let me try it then I reply you with it's status. Best Regards.