Display one "X" at a time in multiple cells

[On the grind]

Ok, I have four cells in a row (A1, B1, C1 & D1). I want the letter "X" in only one of these cells at a time, so when I put an "X" in A1, the other cells don't display anything. Then if I want to change that and put an "X" in cell C1, the "X" that was in cell A1 goes blank (and the others would already be blank).

I hope I explained what I'm looking for good enough to get an answer.

Thanks for any help!

-Paul

 

[MickG]

Try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim Temp As String
Set rng = Range("A1:D1")
Application.EnableEvents = False
If Not Intersect(Target, rng) Is Nothing Then
    Temp = Target
    rng = ""
    Target = Temp
End If
Application.EnableEvents = True
End Sub

Mick

 

[schielrn]

You would have to use VBA for this, something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("a1:d1")) Is Nothing Then
    Application.EnableEvents = False
    Range("a1:a4").ClearContents
    Target.Value = "X"
    Application.EnableEvents = True
End If
End Sub

Hope that helps.

 

[On the grind]

I'm a rookie at excel. So where do I type all this?

 

[schielrn]

From Excel, press Alt+F11 and it should bring up the Microsoft Visual Basic screen. Then you will want to double click on the sheet in the listing on the left hand side that you want this to work on and paste the code into there.

 

[MickG]

Right Click the sheet Tab, Select "View Code" from Short cut Menu.
Vb Window appears.
Paste code into VB window.
Close window.
Place "X" in any cell in Range "A1:D1"
Repeat for another cell in Range, to see code work.
Mick

 

[On the grind]

Awesome! That's cool stuff.

Thanks!

 

[On the grind]

One more thing... I actually want to do this in multiple places on the sheet (like on cells A7, B7, C7, D7 or A22, B22, C22, D22). How would this change the code? I can guess but I want to be sure so I figured I'd ask while you were here online...

Thanks!

 

[MickG]

Probably better ways , but first thoughts:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim Temp As String
Set rng1 = Range("A7, B7, C7, D7") 
Set rng2 = Range("A22, B22, C22, D22") 
Application.EnableEvents = False
If Not Intersect(Target, rng1) Is Nothing Then
    Temp = Target
    rng1 = ""
    Target = Temp
End If
If Not Intersect(Target, rng2) Is Nothing Then
    Temp = Target
    rng2 = ""
    Target = Temp
End If
Application.EnableEvents = True
End sub

Mick

 

[On the grind]

Thank you! Works great.......