Advice needed on clearing cells without removing formulas

[Digital2k]

I would like to enter the letter "Y" in cell A1, and if I enter "Y" in B1,
the "Y" is automatically removed from A1. And if I enter "Y" in C1, "Y" is
cleared from B1.
Is there a formula that can achieve this and allow me to enter data in the
cell without removing the formula?

Please help,
And Thanks,
Digital2k

 

[Ken Johnson]

Hi Digital2k,

I think you will have to use a WorksheetChange Event Procedure to
achieve that.

Something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Range("B1") = "Y" Then Range("A1") = ""
If Range("C1") = "Y" Then Range("B1") = ""
End If
Application.EnableEvents = True
End Sub

This code has to be pasted into the Code Module of the affected
worksheet. If you copy the code then right-click your worksheet's sheet
tab, then choose "View Code" from the popup menu, you can paste it into
the code module that appears.

Your security level will have to be medium (Tools|Macro|Security...
click "Medium" then Save and Close workbook and Re-Open and click
"Enable Macros" on the dialog that appears.

Ken Johnson

 

[Don Guillett]

post your formula

 

[Digital2k]

Thank you Ken,
I did exactly as you described but it did not work for me.
I should have given you more info.
I have a worksheet with 100 rows from 7-106 that I want to include and three
columns S, AC, AI that I want to use for this formula or macro.( I have
other data in the other cells)
If S7 has a "Y" and I insert a "Y" in AC7 I would like "Y" from S7 to be
removed. If I insert a Y in AI7 I would like AC7 to be removed.
In other words There should only be one y in either cell mentioned for that
row.
I would like all 100 rows to have the same function but not effect other
rows. If I insert a y in cell AI7 I want S7 or AC7 to have a y removed, S8
or AC8 should not be effected unless I insert y in that row.
I hope I made myself a little clearer. Is this even possible to do?
Thanks so much in advice
Digital2k

 

[Don Guillett]

right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Or Target.Row > 106 Then Exit Sub
ar = Target.Row
If UCase(Cells(ar, "ac")) = "Y" Then Cells(ar, "s") = ""
If UCase(Cells(ar, "ai")) = "Y" Then Cells(ar, "ac") = ""
End Sub

 

[Ken Johnson]

Hi Digital2k,

Try this which will allow a "Y" in only one of the three cell (S, AC or
AI) for each of the rows from row 7 down to row 106



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Y" Then
If Not Intersect(Range("S7:S106,AC7:AC106,AI7:AI106"), _
Target) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
Select Case Target.Column

Case Range("S:S").Column
Cells(Target.Row, Range("AC:AC").Column).ClearContents
Cells(Target.Row, Range("AI:AI").Column).ClearContents

Case Range("AC:AC").Column
Cells(Target.Row, Range("S:S").Column).ClearContents
Cells(Target.Row, Range("AI:AI").Column).ClearContents

Case Range("AI:AI").Column
Cells(Target.Row, Range("S:S").Column).ClearContents
Cells(Target.Row, Range("AC:AC").Column).ClearContents

End Select
End If
Application.EnableEvents = True
End If
Exit Sub
ERRORHANDLER: Application.EnableEvents = True
End Sub

If you want to remove the case sensitivity so that Y or y will work
then change the second line to...

If Ucase(Target.Value) = "Y" Then


Ken Johnson

 

[Digital2k]

Ken, You are the MAN!!
That was brilliant!
Thank you very much that worked great!
Digital2k

 

[Digital2k]

Thanks again,
One more thing, When I clear contents on a row or entire sheet, I get a
run-time error:
Run-time error '13':
type mismatch
When I debug, the second line in your formula is highlighted.
If Ucase(Target.Value) = "Y" Then
Any clues on this issue?
Digital2k

 

[Don Guillett]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Or Target.Row > 100 Then Exit Sub
ar = Target.Row
Application.EnableEvents = False
If UCase(Cells(ar, "c")) = "Y" Then Cells(ar, "b") = ""
If UCase(Cells(ar, "d")) = "Y" Then Cells(ar, "c") = ""
Application.EnableEvents = True
End Sub

 

[Don Guillett]

If Ucase(Target.Value) = "Y" Then
try
If Ucase(Target) = "Y" Then

 

[Dave Peterson]

Comparing Target.value to a single value doesn't make sense to excel if you're
changing multiple cells.

You could add this to the top of the routine to just quit if there is more than
one cell changed:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub
.....rest of code

 

[Digital2k]

Thank You Mr. Peterson,
That solved my problem.
Digital2k