The enter key and the cell

Alexxx12 · #1 (**permalink**) 07-16-04, 20:05

I have a sheet in which repeatitive values between PM, AM or AM/PM can be entered in one column J. If the user enters either one of the values and the value count has reach 20, then a messagebox should popup stating that no more values can be entered. and all the cells in that column should lock so that the user can not force an entry.

Your anticipated help is greatly appreciated.

Alexxx

Kicker · #2 (**permalink**) 07-17-04, 02:33

I have an idea but would like to see an example of what you are calling a worksheet. You could use the worksheet_selectionchange and set a range for J1:J20. then, if you are within the range, call the sub and do a countif().

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range

    Set myRange = Intersect(Range("J1:J02"), Target)
    If Not myRange Is Nothing Then
            'add some code
    endif
end sub

As I said, this is just a thought. Can you post an example showing exactly what the worksheet cell entries would look like?

ttfn
Kicker

Alexxx12 · #3 (**permalink**) 07-17-04, 09:12

Here is the example:

H I J K
_____________________________________________
Date Sch. Date Att. AM/PM SW Exp Date
11/4/2004 AM 8/31/2004
11/4/2004 PM 3/31/2005
11/4/2004 AM 12/31/2004
11/4/2004 AM 12/31/2004

The moment the AM count has reached 20, for instance, and the user enters 'AM' in the next J cell, then a msgbox comes up telling the user the count if full. This has to work for all sheets withing the workbook.

Also do you know anything about Auto_activate to make this action available the moment the workbook is opened?

Kicker · #4 (**permalink**) 07-17-04, 12:37

Only had a few minutes to play with this as I have to go to work.

The following works for a specific sheet. You could copy it to every sheet if necessary or play with it for the workbook. to make things easier, I use some "hidden and out of the way cells" to hold calculations like this. L2 and L3 would be used to hold the AM and PM counts so the user could see exactly how many they have before making any entries. Or, you could modify the code to calculate the countif() on the fly. Your choice.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
    Set myRange = Intersect(Range("J:J"), Target)
    If Not myRange Is Nothing Then
        If Range("L2").Value => 20 Then
            MsgBox ("You have 20 scheduled")
        End If
        If Range("L3").Value => 20 Then
            MsgBox ("You have 20 scheduled")
        End If
    End If
End Sub

Cell L2 and L3 have this formula
=COUNTIF(J:J,"AM") and =COUNTIF(J:J, "FM") respectively

Good luck. I will check back tonight

ttfn
Kicker

Internet Services

Web Development

Digital Marketing

Consumer Tech