Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]() 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? -- Lady Dungeness Crabby, but Great Legs! |
#2
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
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 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) "Lady Dungeness" wrote in message ... 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? -- Lady Dungeness Crabby, but Great Legs! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can a form made in Excel 2002 be converted into a fillable form? | Excel Discussion (Misc queries) | |||
help with order form function | Excel Worksheet Functions | |||
Excel Insert Function form | Excel Worksheet Functions | |||
Recall function to a User Form | Excel Discussion (Misc queries) | |||
Can you use the validate function in a data form in Excel? | Excel Worksheet Functions |