Sub to ensure a single, latest input of "x" in col G

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Looking for a sub which can ensure that only a single, latest "x" can be
input at any one time within col G. If say, G2 already contains: x, and I
input another: "x" into G4, the sub should clear G2 before accepting G4's
input. Something like a toggle. Thanks
 
Assuming the only text in Column G is to be this single X, then this event
code should do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
Columns("G").Clear
Target.Value = "X"
Application.EnableEvents = True
End If
End Sub

To install it, right click the tab at the bottom of the worksheet that is to
have this functionality, select View Code from the popup menu that appears
and then copy/paste the above code into the code window that opened up. Now
go back to the worksheet and type in your X (actually, as written, any text
will do) in any cell in Column G, then put the X in a cell in Column G.
 
This might be slightly better to use...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Target.Count = 1 Then
Application.EnableEvents = False
Columns("G").Clear
Target.Value = "X"
Application.EnableEvents = True
End If
End Sub

It protects your user selecting a series of cells in Column G and hitting
the Delete button (doing that with my originally posted code will fill all
the selected cells with X's).
 
Back
Top