Form or Function help, please?

  • Thread starter Thread starter Lady Dungeness
  • Start date Start date
L

Lady Dungeness

I've got an Excel worksheet set up where there are two columns for each day
of the month. There are ten rows. A user should put an X (I'm using a
colored-fill) in just ONE box in each column every day. The user should be
able to change where to put the X, but shouldn't be able to enter more than
one single sole solitary X in each column.

I have no idea how to go about this.

Right now, the sheet allows the user to enter anything they want in all ten
columns.

I also want to limit what the user can enter into the box they choose -- I'd
prefer for them to choose a box and have it color-filled with black or dark
blue, whatever; and for all the rest of the boxes/rows in that particular
column to then prohibit entry.

Am I making sense? Am I on the right group?
 
Try this


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:BI11" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Interior.ColorIndex = xlColorIndexNone
ElseIf .Value <> "X" Then
.Value = ""
ElseIf Application.CountIf(Me.Cells(2, .Column).Resize(10), "X")
1 Then
Click to expand...
MsgBox "Column already marked"
.Value = ""
Else
.Interior.ColorIndex = 38
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top