Allow user entry in one cell (out of three cells)

Question

I am looking for a check in excel whereby if a user has input a value in 1 out of three cells, then the remaining two cells should not allow data entry. I have looked at DATA VALIDATION but can't find what I am looking for. Thank you for helping me out.

Use the `Worksheet_Change` event. Give it a try and if you are stuck then post the code that you tried and we will take it from there. — Siddharth Rout, Oct 21 '13 at 13:58
Will something like this make sense? Private Sub Worksheet_Change(ByVal Target As Range) If Range("$E11") = 1 Then ActiveSheet.Range("F11:G11").Cells.Locked = True End If End Sub — Kashif77, Oct 21 '13 at 14:17
If you want your user to be able to change their value, you could lock all three cells on `Worksheet_Change` and unlock the cell they changed afterwards. Also, you should edit your question so that your code from your comment is in it. — Ratafia, Oct 21 '13 at 14:40

score 2 · Accepted Answer · answered Oct 21 '13 at 14:42

This is an example using cells A1, B1, and C1.

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rLook As Range
    Set rLook = Range("A1:C1")
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    If Intersect(Target, rLook) Is Nothing Then Exit Sub
    If wf.CountA(rLook) < 2 Then Exit Sub
    Application.EnableEvents = False
        Target.Clear
        MsgBox "Only one entry allowed"
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

+ 1 :) However, you might also want to see [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) since you are working with `_Change` event? — Siddharth Rout, Oct 21 '13 at 16:02
Depending on what you are looking for, you can also use form control for example option button. But since you had cells issue it is just a comment ;) — lowak, Oct 22 '13 at 06:50

Allow user entry in one cell (out of three cells)

1 Answers1