Forcing User inputs

[apache007]

Hi All,

I need to force user inputs when inserting data on a row.

Example:

I have 3000 rows of data that needs to be Entered by a user into the worksheet

Transaction 1: A1, B1, C1, D1, E1
Transaction 2: A2, B2, C, D2, E2
etc....

How do I force the user to enter the data of transaction 1 in the order I
like ??
Ex. A1 first, then B1, then C1, then D1, then E1
Otherwise ERROR

OR

How do I force the user to enter CELL B1, before able to enter CELL D1 or E1
??


Thanks in advance.

 

[JLatham]

This isn't perfect, it doesn't actually test to determine the specific cell
that must next have data entered into it. That is, if the whole range is
empty and you choose cell E99, it will tell you that you have to enter data
into D99 first. True in its own right, but not accurate since they haven't
even entered data into A1 yet. But...

This code goes into the worksheet's event code module: right-click on the
worksheet's name tab and choose [View Code] and then copy and paste this code
into that module and give it a try. There's a second version below, also

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 5 Then
'not in A:E, ignore
Exit Sub
End If
Select Case Target.Row
Case Is = 1
'row 1, and A1 are special cases
If Target.Column = 1 Then
'in A1, ignore
Exit Sub
End If
'you are in B1:E1
If IsEmpty(Target.Offset(0, -1)) Then
'optional message
MsgBox "Don't get ahead of yourself, enter data into " _
& "cell " & Target.Offset(0, -1).Address & " first."
Application.EnableEvents = False
Target.Offset(0, -1).Activate
Application.EnableEvents = True
Exit Sub
End If
Case Else
'you are in a row below row 1
If Target.Column = 1 Then
'check E in row above
If IsEmpty(Target.Offset(-1, 4)) Then
MsgBox "Don't get ahead of yourself, enter data into " _
& "cell " & Target.Offset(-1, 4).Address & " first."
Application.EnableEvents = False
Target.Offset(-1, 4).Activate
Application.EnableEvents = True
Exit Sub
End If
Else
'in B, C, D or E
If IsEmpty(Target.Offset(0, -1)) Then
'optional message
MsgBox "Don't get ahead of yourself, enter data into " _
& "cell " & Target.Offset(0, -1).Address & " first."
Application.EnableEvents = False
Target.Offset(0, -1).Activate
Application.EnableEvents = True
Exit Sub
End If
End If
End Select

End Sub

**** Second Version ****
This one doesn't give any messages, but it does force them back to the very
next cell that requires data.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 5 Then
'not in A:E, ignore
Exit Sub
End If
Select Case Target.Row
Case Is = 1
'row 1, and A1 are special cases
If Target.Column = 1 Then
'in A1, ignore
Exit Sub
End If
'you are in B1:E1
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1).Activate
Exit Sub
End If
Case Else
'you are in a row below row 1
If Target.Column = 1 Then
'check E in row above
If IsEmpty(Target.Offset(-1, 4)) Then
Target.Offset(-1, 4).Activate
Exit Sub
End If
Else
'in B, C, D or E
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1).Activate
Exit Sub
End If
End If
End Select

End Sub