Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing User inputs
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing User inputs
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 "apache007" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking Cells when user inputs data | Excel Discussion (Misc queries) | |||
Extend Border As User Inputs New Rows | Excel Discussion (Misc queries) | |||
Forcing Input from a user | Excel Worksheet Functions | |||
Forcing the user to make entries in a specified worksheet | Excel Discussion (Misc queries) | |||
If the user inputs lower case y/n answer How do I convert automat. | Excel Discussion (Misc queries) |