Imagine an Excel workbook with columns A, B, and C.  In column A, I
have limited the data entries in each cell A1, A2 etc with a drop down
box.  Once the user makes their selection in the A column, they will
have to enter number data in EITHER the B or C column.  The column they
choose (B or C) strictly depends on their answer in A, but right now,
the user can freely enter data into either column.  How do I prohibit
errors by forcing them to the correct column?

Here's an example.  In column A, the user will select an emotion via
drop down box.  Greed, Fear, Happiness, Love etc.  Those emotions fall
into two broad categories: "Good" or "Bad".  I've already decided
what's good and bad based on some research factors--some of which the
user may not agree
with, and are part of the research--in any case, back to the question.

Good and Bad will be Columns B and C.  After selection, I want the
user to type the number of times in a month they feel that emotion, so
this is a cell with no real restrictions on entry, except that it must
be a number.

So, when they select "happiness" in Column A, a "Good" emotion, they
MUST enter their rating in column B--NOT column C which is only for
"Bad" emotions.  Right now, they can enter a rating in either column.

Note: I do not want an annoying pop-up error message each time.  I
simply want to have the incorrect cell locked or otherwise unavailable.

Thanks,

Herkdrvr

Hello Herkdrvr,

I have attached a visual basic macro that does what you've asked. It specifically
 - has a function to determine "good" or "bad" (and can be extended
for other words)
 - enforces the cells in column 1 are acceptable values
 - if the value in column 1 is "good", it unlocks the second column &
locks the third (plus erases any previous value)
 - if the value in column 1 is "bad", it locks the second column (and
erases the previous value) & unlocks the third column
I am not quite sure which annoying pop-up error message you are
referring to. If it is still present, please describe it in a
clarification request and I'll be glad to eliminate it (if feasible).

To add the following macros to your worksheet you will need to:
 - set your security preferences to allow macros to be executed (low
or medium). [if you can't run these macros, identify your version of
Excel and I'll look up the method to make this change]
 - Use the menu Tools -> Macro -> Visual Basic Editor to bring up Visual Basic
 - In Visual Basic, right click on the worksheet listed in the "module
window". A pop up menu should appear and select "View Code".
At this point, a window should appear with an empty procedure.
 - Copy / paste the code at the end of this answer to replace that procedure.
At this point, you may want to make some slight changes to the code. These include:
 o Two functions "Worksheet_Change" and "Worksheet_SelectionChange"
refers to a range A2:A10 - change this to match the range of values of
"good / bad" emotions input by the users.
 o Function "isGood" has a series of IF statements, add the other
emotions using the same coding style to return the appropriate true /
false values.
 o If you move the "Good" or "Bad" columns, adjust the offsets in the
lines that lock / unlock individual cells.
 o If you set a password to protect the worksheet, you need to add it
to the active sheet protect / unprotect lines. Let me know if you need
help setting the password.
Once this is done, I suggest you close & return to Microsoft Excel (a menu option).

The sample spreadsheet I used had the following structure.
 Column A - set up using data validation to allow values from a "list"
 Column B - the "good" column, set up using data validation to allow
whole numbers from 0 to 31
 Column C - the "bad" column, set up using data validation to allow
whole numbers from 0 to 31
 Column D - a "hidden" column that has a formula referring to the value in column A
  e.g., the formula in D2 is =A2, the formula in D3 is =A3, and so on
[I am triggering off of the "recalculate event" in Excel - if you
already have calculations referring to the emotion values, you don't
need to have this]
 Column G (or wherever) - the list of emotions referred to in the
Column A data validation
All of these cells should be "locked" (the default in Excel). Use
Format -> Cells -> Protection and make sure Locked is selected.

At this point, protect the password with menu
  Tools -> Protection -> Protect Worksheet...
and make sure contents are protected. I did NOT use a password; as
indicated above, revise the macro if you use one.

At this point, everything else should be automatic. When you click on
one of the emotion values in column A, the Worksheet_SelectionChange
remembers that selection. When a value is changed, the
Worksheet_Calculation macro will update the relevant cells based on
the selected value. Tabbing between cells will skip the protected
cells (helping to avoid errors in entering the data).

If you have any difficulty with the macros or if part of the answer is
unclear, please make a clarification request. I will be glad to make
any necessary corrections so you are fully satisfied.

Good luck with your research.
  --Maniac

Macros follow, note that Worksheet_Change might not be needed. Based
on the description of the function I tried this method first. However,
in the version of Excel I tested with, that only works if the cells
are changed w/o data validation (very odd - change tracking notices
the cells changing, but this function isn't triggered). It may work
better for you, so I left it in (it is a simpler solution than the
alternative).

Public CCell As Range
Public VCell As Boolean

Private Sub Worksheet_Calculate()
    If Not VCell Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    If isGood(CCell.Value) Then
        ActiveSheet.Cells(CCell.Row, CCell.Column + 1).Locked = False
        ActiveSheet.Cells(CCell.Row, CCell.Column + 2).Value = ""
        ActiveSheet.Cells(CCell.Row, CCell.Column + 2).Locked = True
    Else
        ' Is "bad", lock next column & unlock the following one
        ActiveSheet.Cells(CCell.Row, CCell.Column + 1).Value = ""
        ActiveSheet.Cells(CCell.Row, CCell.Column + 1).Locked = True
        ActiveSheet.Cells(CCell.Row, CCell.Column + 2).Locked = False
    End If
    Application.EnableEvents = True
    ActiveSheet.Protect
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    ' Only activate if in area with our special values
    If Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    ' Determine if it is "good or bad"
    If isGood(Target.Value) Then
        ' Is "good", unlock next column & lock the following one.
        ActiveSheet.Cells(Target.Row, Target.Column + 1).Locked = False
        ActiveSheet.Cells(Target.Row, Target.Column + 2).Value = ""
        ActiveSheet.Cells(Target.Row, Target.Column + 2).Locked = True
    Else
        ' Is "bad", lock next column & unlock the following one
        ActiveSheet.Cells(Target.Row, Target.Column + 1).Value = ""
        ActiveSheet.Cells(Target.Row, Target.Column + 1).Locked = True
        ActiveSheet.Cells(Target.Row, Target.Column + 2).Locked = False
    End If
    Application.EnableEvents = True
    ActiveSheet.Protect
    VCell = False
End Sub

Function isGood(X As String) As Boolean
    
    isGood = False
    If X = "Greed" Then
        isGood = False
    ElseIf X = "Fear" Then
        isGood = False
    ElseIf X = "Happiness" Then
        isGood = True
    ElseIf X = "Love" Then
        isGood = True
    End If
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then
        VCell = False
    ' Only activate if in area with our special values
    ElseIf Intersect(Target, Range("A2:A10")) Is Nothing Then
        VCell = False
    Else
        VCell = True
        Set CCell = Target
    End If
End Sub

Answered to perfection.  Solves my problem.  When I have another Excel
quandry, I'll know who to ask.  THANK YOU!!!

I have tried the attached VBA programme. But I cannot trigger the macro.

After I click on one of the emotion values in column A, a message box
emerge and tell me that the sheet has been protected and must be
firstly unprotected to edit.

So, could Maniac tell me why this happens?

Hello Jeffzhou,

There could be several possible causes to the symptom you describe.
I'd say most likely the worksheet has a password (I said to leave that
blank). If that's not the cause - and you need an answer - I suggest
you post a question with a more complete explanation of the steps you
performed and the symptoms you are seeing.

  --Maniac