Hi,

I need to set up an excel spreadsheet with let's say 4 columns;
A1,B1,C1,D1.
The user cannot enter data into the any column unless all the previous
columns are completed.
They should be prevented from doing so and an error msg should appear
warning them.
Therefore it's a sequential flow - A first then B then C etc.

I hope someone can help me!!
Thanks in Advance
Carol

Carol,
One possibility.

This code must be placed in worksheet where the data is entered. Right click
on the worksheet tab, "View Code" and copy/paste. It currently looks at
A1,B1,C1 and D1 and so may need modifying for your specific needs.

HTH

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo exitsub

Select Case Target.Address
Case "$A$1"
' Do nothing .....
Case "$B$1"
If Cells(1, 1) = "" Then ' A is blank
MsgBox "You must enter data in A1 first"
Target.Value = ""
End If
Case "$C$1"
If Application.Or(Cells(1, 1) = "", Cells(1, 2) = "") Then ' A and/or B
is blank
MsgBox "You must enter data in A1 and B1 first"
Target.Value = ""
End If
Case "$D$1"
If Application.Or(Cells(1, 1) = "", Cells(1, 2) = "", Cells(1, 3) = "")
Then ' A,B and/or C is blank
MsgBox "You must enter data in A1,B1 and C1 first"
Target.Value = ""
End If
End Select

exitsub:
Application.EnableEvents = True

End Sub

"Carol" wrote:

Hi,

I need to set up an excel spreadsheet with let's say 4 columns;
A1,B1,C1,D1.
The user cannot enter data into the any column unless all the previous
columns are completed.
They should be prevented from doing so and an error msg should appear
warning them.
Therefore it's a sequential flow - A first then B then C etc.

I hope someone can help me!!
Thanks in Advance
Carol

Hi Carol,

Try this:

On cell B1 apply Data validation: Custom, Formula: =NOT(ISBLANK(A1))
On cell C1 apply Data validation: Custom, Formula: =NOT(ISBLANK(B1))
etc. Uncheck Check Box Empty cells!

Regards,
Stefi



€žCarol€� ezt Ã*rta:

Hi,

I need to set up an excel spreadsheet with let's say 4 columns;
A1,B1,C1,D1.
The user cannot enter data into the any column unless all the previous
columns are completed.
They should be prevented from doing so and an error msg should appear
warning them.
Therefore it's a sequential flow - A first then B then C etc.

I hope someone can help me!!
Thanks in Advance
Carol

Hi Carol,
Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count 1 Or Target.Rows.Count 1 Then
Range("E1").Select
GoTo MESSAGE
End If
Application.EnableEvents = False
Select Case Target.Column
Case 2
If Cells(Target.Row, 1) = "" Then
Range("E1").Select
Application.EnableEvents = True
GoTo MESSAGE
End If
Case 3
If Cells(Target.Row, 2) = "" Then
Range("E1").Select
Application.EnableEvents = True
GoTo MESSAGE
End If
Case 4
If Cells(Target.Row, 3) = "" Then
Range("E1").Select
Application.EnableEvents = True
GoTo MESSAGE
End If
End Select
Application.EnableEvents = True
Exit Sub
MESSAGE: MsgBox "Columns A,B,C and D must be filled sequentially!"

End Sub

E1 is selected and the message is displayed whenever any of the
following occur:

A range greater than 1 cell is selected
A column B cell is selected and the column A cell in the same row is
empty
A column C cell is selected and the column B cell in the same row is
empty
A column D cell is selected and the column C cell in the d\same row is
empty

The code must be pasted into a worksheet code module

Ken Johnson

Hi,

1) Right-click on the sheet tab of the worksheet (by default it's something
like 'Sheet1') and click ''View Code
2) This opens the class module for the worksheet. This is where you can put
code that responds to events on the worksheet (like cell changes)
3) paste in this code (Worksheet_Change event handler):

CODE
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iRow As Integer
Dim iCol As Integer
Dim iCheckCols As Integer
Dim sColumns As String
Dim bFailed As Boolean

iRow = Target.Row
iCol = Target.Column

If iCol = 1 Or iCol 4 Then Exit Sub
If VBA.IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False

For iCheckCols = iCol To 1 Step -1
If VBA.IsEmpty(Cells(iRow, iCheckCols)) Then
sColumns = sColumns & iCheckCols & ","
bFailed = True
End If
Next

If VBA.Right(sColumns, 1) = "," Then sColumns = VBA.Left(sColumns,
VBA.Len(sColumns) - 1)

If bFailed = True Then
MsgBox "Please complete Column(s) " & sColumns & " first",
vbCritical + vbOKOnly
Target.Clear
Application.EnableEvents = True
End If
Application.EnableEvents = True

End Sub
<<<< END CODE

HTH

Philip
"Carol" wrote:

Hi,

I need to set up an excel spreadsheet with let's say 4 columns;
A1,B1,C1,D1.
The user cannot enter data into the any column unless all the previous
columns are completed.
They should be prevented from doing so and an error msg should appear
warning them.
Therefore it's a sequential flow - A first then B then C etc.

I hope someone can help me!!
Thanks in Advance
Carol

That's great - thanks to everyone for their kind help.

C.