You are here:

Excel/Macro to check whether proper values have been entered based on selections in drop down on Save

Advertisement


Question
Hi Adelaide,

I am working on a excel sheet wherein there are 5 drop down cells namely (A2,B2,M2,N2,P2). Now based on the selections in cells A2 & B2 the drop - down list in the remaining three cells gets populated and the user can select from the available list.

For a single combination in cells in A2 & B2 there can be multiple possibilities in the remaining cells

For example:-    
A2:- Fruit B2:- Apple  M2:- Red    N2:- Sweet P2:- small
A2:- Fruit B2:- Apple  M2:- Green  N2:- Ripe  P2:- Big
A2:- Fruit B2:- Apple  M2:- Reddish white  N2:- Natural P2:- vary

As per the above example for that combination (A2 & B2) the user can select any of the three values from the drop down list.  

The problem am facing is if user changes the combination in A2 & B2 and doesn't change the selection in the remaining three drop-downs or copy pastes the values and mismatch occurs then my entire excel sheet is messed up.

For example (Mismatch)
A2:- Fruit B2:- Apple  M2:- Red  N2:- Sweet P2:- small
A2:- Fruit B2:- Apple  M2:- Red  N2:- Ripe  P2:- Big  OR
A2:- Fruit B2:- Apple  M2:- Red  N2:- Sweet P2:- vary

Appreciate if you could help me with a macro that checks the entire sheet for such possible errors and ensures that proper entries are made for the combinations in when user clicks On Save (On_Save event) and gives an error message or highlights the error row or cell.

Any help would be appreciated.

Thanks & Regards
Sourabh

Answer
Hi Sourabh,

I see this question was placed in the Question Pool for Excel and not assigned to an Expert.

According to your needs, I would recommend you using a Worksheet_Change event and using IF calls to define what you would like.

For Example:

----Code----
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim KeyCells As Range
   Set KeyCells = Range("A2")  'A2 is the reference of the dropdown
   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then
       
   If Cells("A2").Value = "1" Then   '1 is the value you are seeking
   Range("B1").Select   'B1 is the new dropdown you will create
   With Selection.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:="=H7:H11"   'h7:h11 is the range of your validation list
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = ""
       .InputMessage = ""
       .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
   Else If Cells("A2").Value = "2" Then   '2 is the value you are seeking
   Range("B1").Select   'B1 is the new dropdown you will create
   With Selection.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:="=i7:i11"   'i7:i11 is the range of your validation list
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = ""
       .InputMessage = ""
       .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
   End If
End Sub
----Code----

As written, the above code checks th value in Cell A2 and creates a new dropdown of appropriate choices in B2.  Once you have this code setup as you need it, you would then change the values to reference B2 and then provide appropriate choices based on the selection in B2, and so on.

If you need to add more than 2 possible choices for each dropdown, you can copy paste the Else If to .ShowError = True lines and repaet them until you have exhuasted all posibble choices.

If you need more guidance, please let me know.

Steve
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Steve

Expertise

I am proficient in all areas of MS Excel, including Visual Basic (Macros), Charting, Graphing, Custom User Forms, Template Layout and Creation, Developer Add-ins, Active-X Controls, Setting Advanced Properties, XML Data and Workbook Protection.

Experience

I have an extensive educational, vocational and business background extending over 12 years in information technology.

Organizations
Association of Information Technology Professionals IEEE Computer Society Texas Professionals Texas Young Professionals Excel Developers Excel Blackbelts

Education/Credentials
Microsoft Certified Technology Specialist (MCTS) Program Microsoft Office Expert Program

©2016 About.com. All rights reserved.