VBA Express : Excel - Mutually Exclusive Cells (Toggling Cells)

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) 
     'This event will make a group of three cells mutually exclusive
    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) 
     'This code will make a group of 2 cells mutually exclusive.
    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 
     'Note: The use of this syntex for 2 cells was first pointed out to me by smozgur back in 2002 on another forum.
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.