conflict in data of columns

mrxdm

New Member
Joined
Jan 2, 2018
Messages
20
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
MedicineTmp No
Mohrak1
Molyen5
Shadeed1
Molyen1
Seelani6
Moqvee2
Taryaq2
Mohrak3
Moshel1
Akseer1
Moqvee1
Taryaq1
Jwarish Imli2
Hab Sabir3
Mohrak2
Shadeed2
Molyen2
Moshel2
Ojaee5
Atreefal1
Molyen3
Moshel3
Akseer3
Molyen6
Molyen4
Hazim5
Foladi5
Soosi5
Taryaq meda5
Akseer Itfaal2
Akseer Jigar4
Hab Mqvee Khas4
Akseer Jadeed4
Moqvee3
Taryaq3
Akseer2
Shadeed3
Molyen Khas5
Hab Shifa6

<colgroup><col><col></colgroup><tbody>
</tbody>
(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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,807
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
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

New Member
Joined
Jan 2, 2018
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,512
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top