Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Form or Function help, please?


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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Form or Function help, please?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can a form made in Excel 2002 be converted into a fillable form? Paraclete Excel Discussion (Misc queries) 1 February 20th 07 09:20 PM
help with order form function westie13 Excel Worksheet Functions 0 August 3rd 06 04:42 PM
Excel Insert Function form Santa-D Excel Worksheet Functions 2 June 27th 06 06:26 PM
Recall function to a User Form shnim1 Excel Discussion (Misc queries) 1 April 12th 06 07:47 AM
Can you use the validate function in a data form in Excel? Jolly Excel Worksheet Functions 0 December 12th 05 11:00 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"