Help with code

[Samuel Looney]

I am trying to write a conditional statement that will allow the users to
enter a value in only one cell. I created a function and am calling it from
the worksheet.
Function onChange()
Dim x As Integer
Dim y As Integer
x = ActiveCell.Row
y = ActiveCell.Column
If Cells(x, 1).Value <> "" Then ' checks to make sure selected row
contains an Item
If y >= 8 Then
If y <= 10 Then
Cells(x, 8).Value = ""
Cells(x, 9).Value = ""
Cells(x, 10).Value = ""
End If
End If
End If
End Function
I am trying to make sure that only 1 of these cells can contain a value.
I called this function on the double click event. It works great... except
the user can use the arrow key or single click to leave the cell that was
double clicked or simply enter any of the cells using arrow key or single
click, thus rendering my coade useless. I need to find a way to ensure that
if they leave one of these cells that this code will still run. The users
could add records on any of the sheets so I have to keep my code generic.
Meaning the rows may change but the column indexes will always be the same.
I took Visual Basic in college for VB.Net. Trying to wrap my mind around VBA
is killing me. Does anyone have any suggestions?

 

[Rick Rothstein]

What about trying this Change event code (you will not need your function
nor will you be using Data Validation... this event code will be doing
everything)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Rows("8:10")) Is Nothing Then
If WorksheetFunction.CountA(Target.EntireRow) > 1 Then
MsgBox "Only one cell can be filled in on this row!", vbExclamation
Application.EnableEvents = False
Target.Value = ""
Target.Select
Application.EnableEvents = True
End If
End If
End Sub

To install it, right click the tab at the bottom of the worksheet where you
want it to apply to and copy/paste the above code into the code window that
appeared. Of course, you can change the warning message that appears to
whatever you want it to say.

 

[Samuel Looney]

Rick,
Thanks so much for the help. I tried to use this code and it worked for
Rows" 8:10" I tried to modify it so it will work on any row that does not
have a blank value in the Active Cell's Row. I am having trouble getting the
syntax right on the CountA line. I would like for it to only count columns
with an index of 8:10 for the row that the cell is active in.
I appreciate any further suggestions, as I tried to modify this for many
hours today, reading the help section, and trial and error with still no luck.

 

[Rick Rothstein]

Sorry, I misread your original question to be asking for rows, not columns
(my fault on that, not yours). If I understand what you want correctly, give
this event code a try instead of what I posted before...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("H:J")) Is Nothing Then
With Target
If WorksheetFunction.CountA(Range("H" & .Row).Resize(1, 3)) > 1 Then
MsgBox "Columns H:J can have only one cell filled!", vbExclamation
Application.EnableEvents = False
.Value = ""
.Select
Application.EnableEvents = True
End If
End With
End If
End Sub