SOLVED

Excel mandatory cell with dropdown menu

%3CLINGO-SUB%20id%3D%22lingo-sub-1232219%22%20slang%3D%22en-US%22%3EExcel%20mandatory%20cell%20with%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232219%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20basic%20information%20in%20let's%20say%20columns%20M%2C%20N%20and%20O.%3CBR%20%2F%3EIn%20column%20P%20I%20have%20a%20dropdown%20menu%20consisting%20of%203%20different%20names.%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20now%20ist%3C%2FP%3E%3CP%3E1)%20that%20if%20any%20data%20in%20columns%20M%2C%20N%20or%20O%20get%20changed%2C%20it%20has%20to%20be%20mandatory%20to%20select%20a%20different%20name%20in%20column%20P%20than%20the%20existing%20one.%3C%2FP%3E%3CP%3E2)%20Or%20that%20if%20any%20data%20in%20columns%20M%2C%20N%20or%20O%20get%20changed%2C%20a%20Msg%20box%20appears%20to%20remind%20the%20user%20that%20one%20has%20to%20select%20a%20different%20option%20in%20column%20P%20than%20the%20existing%20one%2C%20even%20if%20there%20is%20nothing%20yet%20in%20that%20cell.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20in%20advance.%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1232219%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232232%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20mandatory%20cell%20with%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232232%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20do%20this%20with%20a%20very%20small%20macro%20on%20the%20page%20itself%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EPrivate%20Sub%20worksheet_Change(ByVal%20target%20As%20Range)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EIf%20Not%20Intersect(target%2C%20Range(%22M%3AO%22))%20Is%20Nothing%20Then%3CBR%20%2F%3EMsgBox%20(%22You%20must%20now%20change%20the%20dropdown%20in%20column%20P%22)%3CBR%20%2F%3EElse%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EEnd%20Sub%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20-%20press%20Alt%20F11%20to%20open%20the%20VBA%20editor%20and%20see%20the%20code%20in%20situ.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232262%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20mandatory%20cell%20with%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232262%22%20slang%3D%22en-US%22%3E%3CP%3ETop%20notch!%20Works%20like%20a%20charm.%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20help.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2056819%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20mandatory%20cell%20with%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2056819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BI'm%20new%20to%20VBA%2C%20how%20can%20i%20just%20make%20the%20dropdown%20mandatory%3F%20this%20is%20a%20table%20where%20data%20entry%20is%20ongoing%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone

 

I have basic information in let's say columns M, N and O.
In column P I have a dropdown menu consisting of 3 different names.

What I would like now ist

1) that if any data in columns M, N or O get changed, it has to be mandatory to select a different name in column P than the existing one.

2) Or that if any data in columns M, N or O get changed, a Msg box appears to remind the user that one has to select a different option in column P than the existing one, even if there is nothing yet in that cell.

Thank you very much in advance.
 

3 Replies
best response confirmed by Uguey (New Contributor)
Solution

You can do this with a very small macro on the page itself:

 

Private Sub worksheet_Change(ByVal target As Range)

If Not Intersect(target, Range("M:O")) Is Nothing Then
MsgBox ("You must now change the dropdown in column P")
Else
End If

End Sub

 

I have attached an example - press Alt F11 to open the VBA editor and see the code in situ.

Top notch! Works like a charm.
Thank you very much for your help.@Savia 

@Savia I'm new to VBA, how can i just make the dropdown mandatory? this is a table where data entry is ongoing