|
|
|
|
|
|
Excel
|
Mutually Exclusive Cells (Toggling Cells)
|
|
Ease of Use
|
Intermediate
|
Version tested with
|
2003,2002,2000
|
Submitted by:
|
lenze
|
Description:
|
Occasionally, there is a need to limit entries to only one cell in a group of cells. This technique demonstrates how to make a group of 2 or 3 cells mutually exclusive.
|
Discussion:
|
The code given here is for a WorkSheet_Change Event and goes in the WorkSheet module (Right Click Sheet Tab and choose "view code"). The first code shows how to make 3 cells in a worksheet mutually exclusive. The second code is for a 2 cell scenario.
They can be expanded or combined per user's needs.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Then [A4,A6].ClearContents
If Target.Address = "$A$4" Then [A2,A6].ClearContents
If Target.Address = "$A$6" Then [A2,A4].ClearContents
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$2" Then [B4,B4].ClearContents
If Target.Address = "$B$4" Then [B2,B2].ClearContents
End Sub
|
How to use:
|
- Place the code you need in the WorkSheet module, adjusting ranges as required. Right Click on the sheet tab and enter the code. Enter ALT+Q to close the VBE. Macros must be enabled.
|
Test the code:
|
- After entering the code in the WorkSheet module, test it by making an entry in one of the targeted cells and then a second entry in another targeted cell. The first cells contents will be cleared. The example workbook contains two sheets, each demonstrating one of the above codes.
|
Sample File:
|
Mutually_Exclusive_Cells.zip 5.46KB
|
Approved by mdmackillop
|
This entry has been viewed 209 times.
|
|