Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: The enter key and the cell

    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

  2. #2
    Join Date
    May 2004
    Posts
    14
    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

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    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?

  4. #4
    Join Date
    May 2004
    Posts
    14
    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
    Last edited by Kicker; 07-17-04 at 13:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •