Cell to allow only 1 - 1000 in numeric order

[hajiali]

I want excel to limit A3:A1003 user to only input numbers 1-1000 for their choices form highest to lowest and if they go out of order to alert next value is the missing number

ex: if users input 1,2,3,4,5 and if users accidentally tries to input 7 as next value for excel to prompt message " Next choices needs to be 6"

 

[hajiali]

sounds great. With what you gave me I was able to use that. Thanks bunches

 

[mikerickson]

It sounds like you want the user to select a cell, have a message popup that says "you must enter X" and then have the user enter the number.
If you want to use VBA, why not set it up so that the user double clicks on a cell and the appropriate number is entered.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim keyRange As Range
    Set keyRange = Range("A3:A1002")
    
    If Not Application.Intersect(Target, keyRange) Is Nothing Then
        Cancel = True
        With Target
            If .Value = vbNullString Then
                .Value = Int(WorksheetFunction.Max(keyRange) + 1)
            ElseIf .Value = WorksheetFunction.Max(keyRange) Then
                .Value = vbNullString
            Else
                Beep
            End If
        End With
    End If
End Sub

Note that the user can double click again to remove an entry if they do the wrong cell.

 

[Akuini]

ok I guess thats one way to go about this good thought. However is there a way to do this in a VBA?

Hi, hajiali
If you're still interested using vba to solve your problem, I may write a code to do that.
But, here's a question:
What if you accidentally (or on purpose) change an existing data (in A3:A1003)?
Say 1,2,3,4,5 is already entered, then you accidentally delete number 2, then when you try to type it back to 2, it can't because the maximum value that already exist is 5, so you can only type 6.
How do you want to handle this situation?
One possibilty is to make the existing data can't be edited. I may have an idea how to do that.

 

[hajiali]

Thank mikerickson I like the fact of double clicking It will be less of errors to make. Thank you for Akuini for your help I was able to get it resolved with the previous Reply.

 

[hajiali]

is there a way to use this doubleclick and disable keystroke in those cells?

It sounds like you want the user to select a cell, have a message popup that says "you must enter X" and then have the user enter the number.
If you want to use VBA, why not set it up so that the user double clicks on a cell and the appropriate number is entered.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim keyRange As Range
    Set keyRange = Range("A3:A1002")
    
    If Not Application.Intersect(Target, keyRange) Is Nothing Then
        Cancel = True
        With Target
            If .Value = vbNullString Then
                .Value = Int(WorksheetFunction.Max(keyRange) + 1)
            ElseIf .Value = WorksheetFunction.Max(keyRange) Then
                .Value = vbNullString
            Else
                Beep
            End If
        End With
    End If
End Sub

Note that the user can double click again to remove an entry if they do the wrong cell.

 

[mikerickson]

I don't think there is an Excel approach to disabling key-strokes for cell entry. Worksheet cells are less amenable to our control than userform controls.

 

[Rick Rothstein]

I don't think there is an Excel approach to disabling key-strokes for cell entry. Worksheet cells are less amenable to our control than userform controls.

What about Locking the cells in the column (unLocking everything else) and protecting the sheet... then have your code remove the protection, do its thing and then re-protect the sheet?

 

[mikerickson]

Less extreme than protecting the sheet would be to put Data Validation on the cells with the custom formula =FALSE and an error message like "double clicking is the only data entry method for this cell"

 

[Rick Rothstein]

Less extreme than protecting the sheet would be to put Data Validation on the cells with the custom formula =FALSE and an error message like "double clicking is the only data entry method for this cell"

Data Validation can be circumvented by using copy/paste though.

 

[hajiali]

I got it thanks..