In a worksheet where you have to fill in information what formula to use if
you want to make sure (example cell A1 is filled in before A2) In other words
they if A1 is not filled in you cant move to A2.

To make sure A1 is filled before A2

1. Select A2

2. Enter the below formula in DataValidationCustom and uncheck Ignore blank
=$A$1<""

3. Enter an error message say "Cells A1 should be entered firts" in Data
ValidationError Alert....and click OK.

If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:

In a worksheet where you have to fill in information what formula to use if
you want to make sure (example cell A1 is filled in before A2) In other words
they if A1 is not filled in you cant move to A2.

Hi Jacob,
The formula worked for one worksheet, but the other one already have a data
validation formula and it did not worked. Any other suggestion?
"Jacob Skaria" wrote:

To make sure A1 is filled before A2

1. Select A2

2. Enter the below formula in DataValidationCustom and uncheck Ignore blank
=$A$1<""

3. Enter an error message say "Cells A1 should be entered firts" in Data
ValidationError Alert....and click OK.

If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:

In a worksheet where you have to fill in information what formula to use if
you want to make sure (example cell A1 is filled in before A2) In other words
they if A1 is not filled in you cant move to A2.

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

This is what I need the information to apply only certain cells; not every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

Having dinner and Hockey Game...........Go Pens!

Probably tomorrow after early morning round of golf before I can get back to
this.

Maybe Patrick is free before then<g


Gord

On Tue, 2 Jun 2009 16:40:02 -0700, Link
wrote:

Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

Enjoy your evening! I really appreciate you helping me. Tomorrow will be fine.

"Gord Dibben" wrote:

Having dinner and Hockey Game...........Go Pens!

Probably tomorrow after early morning round of golf before I can get back to
this.

Maybe Patrick is free before then<g


Gord

On Tue, 2 Jun 2009 16:40:02 -0700, Link
wrote:

Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link
wrote:

I am confused. After I click on view code where do I type all this information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what formula to use
if
you want to make sure (example cell A1 is filled in before A2) In other
words
they if A1 is not filled in you cant move to A2.

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on? the
other ranges are aon the same row, so their dependency is on the row above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2) In
other
words
they if A1 is not filled in you cant move to A2.

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on? the
other ranges are aon the same row, so their dependency is on the row above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2) In
other
words
they if A1 is not filled in you cant move to A2.

I have more than one worksheets. For this worksheet I need cell C2 to N4 to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on? the
other ranges are aon the same row, so their dependency is on the row above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2) In
other
words
they if A1 is not filled in you cant move to A2.

I have more than one worksheets. For this worksheet I need cell C2 to N4 to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on? the
other ranges are aon the same row, so their dependency is on the row above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this is
the first with this other set them F:8:O8 and f11:O11. I need to fill f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link
wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2) In
other
words
they if A1 is not filled in you cant move to A2.

not confused at all - but you're feeding more and more into the question

the worksheet change event is in the code behind the sheet
so if you have different cells on other sheets, just copy the code to each
sheets' code page and amend the CONST appropriately.



"Link" wrote in message
...
I have more than one worksheets. For this worksheet I need cell C2 to N4
to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell
n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on?
the
other ranges are aon the same row, so their dependency is on the row
above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same
row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this
is
the first with this other set them F:8:O8 and f11:O11. I need to fill
f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link

wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of
the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2)
In
other
words
they if A1 is not filled in you cant move to A2.

not confused at all - but you're feeding more and more into the question

the worksheet change event is in the code behind the sheet
so if you have different cells on other sheets, just copy the code to each
sheets' code page and amend the CONST appropriately.



"Link" wrote in message
...
I have more than one worksheets. For this worksheet I need cell C2 to N4
to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell
n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on?
the
other ranges are aon the same row, so their dependency is on the row
above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same
row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this
is
the first with this other set them F:8:O8 and f11:O11. I need to fill
f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link

wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of
the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2)
In
other
words
they if A1 is not filled in you cant move to A2.

Hi Patrick,

Maybe I am doing something wrong with this workbook. I did other workbook
with simpler formula and they worked, but this one is not working. I just
have to leave it.
I am just not understanding it.

Thanks for your help.

"Patrick Molloy" wrote:

not confused at all - but you're feeding more and more into the question

the worksheet change event is in the code behind the sheet
so if you have different cells on other sheets, just copy the code to each
sheets' code page and amend the CONST appropriately.



"Link" wrote in message
...
I have more than one worksheets. For this worksheet I need cell C2 to N4
to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell
n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on?
the
other ranges are aon the same row, so their dependency is on the row
above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same
row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this
is
the first with this other set them F:8:O8 and f11:O11. I need to fill
f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link

wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of
the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2)
In
other
words
they if A1 is not filled in you cant move to A2.

Hi Patrick,

Maybe I am doing something wrong with this workbook. I did other workbook
with simpler formula and they worked, but this one is not working. I just
have to leave it.
I am just not understanding it.

Thanks for your help.

"Patrick Molloy" wrote:

not confused at all - but you're feeding more and more into the question

the worksheet change event is in the code behind the sheet
so if you have different cells on other sheets, just copy the code to each
sheets' code page and amend the CONST appropriately.



"Link" wrote in message
...
I have more than one worksheets. For this worksheet I need cell C2 to N4
to
be fill in ordercell c2, then cell J2, then k3, then cell D4, than cell
n4.
after that it goes to F8, then F11, then G8, then G11, then H8 then
H11.......to O8 then O11. Sorry for the confusion.

"Patrick Molloy" wrote:

initial request was for row 2...if the cells above, in row 1 were empty,
then raise the messsage

now you give a range of C2:N4
how does this work? is C2 dependent on C1 and then C3 on C2 and so on?
the
other ranges are aon the same row, so their dependency is on the row
above i
guess.

I'm assuming C2:N4 really should have been C4:N4 --- all in the same
row
for consistency. If this isn't the case, then we can revisit...

so the change is minimal
this
Const WS_RANGE As String = "A2:F2"
to this
Const WS_RANGE As String = "A2:F2,C4:N4,F8:O8,F11:O11"


"Link" wrote in message
...
Hi sorry to be a bother; suppose I have three ranges example c2:n4 this
is
the first with this other set them F:8:O8 and f11:O11. I need to fill
f8
them F11 after G8 then G11 then H8 then H11 etc. ( I want the colum in
order)


"Gord Dibben" wrote:

Patrick's code points only to A1 and A2, not "all cells"

Which cells would you like to see included?

Try editing this to suit or post back with some specifics.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "A2:F2"
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell.Offset(-1, 0)
If .Value = "" Then
MsgBox .Address & " is empty"
End If
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub


Gord

On Tue, 2 Jun 2009 15:35:01 -0700, Link

wrote:

This is what I need the information to apply only certain cells; not
every
cells. Is this possible?

"Gord Dibben" wrote:

You don't type anything.

Copy from Patrick's post and paste into the blank white space of
the
module
that opens when you right-click and "View Code".


Gord Dibben MS Excel MVP

On Tue, 2 Jun 2009 14:55:15 -0700, Link

wrote:

I am confused. After I click on view code where do I type all this
information?

"Patrick Molloy" wrote:

use the sheet's change method ....right click the sheet tab and
select View
Code from the pop-up menu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a2").Address Then
If Range("a1") = "" Then
MsgBox "A1 is empty"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub


"Link" wrote in message
...
In a worksheet where you have to fill in information what
formula to use
if
you want to make sure (example cell A1 is filled in before A2)
In
other
words
they if A1 is not filled in you cant move to A2.

You need to apply this for each worksheet.
--
If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:


Hi Jacob,
The formula worked for one worksheet, but the other one already have a data
validation formula and it did not worked. Any other suggestion?
"Jacob Skaria" wrote:

To make sure A1 is filled before A2

1. Select A2

2. Enter the below formula in DataValidationCustom and uncheck Ignore blank
=$A$1<""

3. Enter an error message say "Cells A1 should be entered firts" in Data
ValidationError Alert....and click OK.

If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:

In a worksheet where you have to fill in information what formula to use if
you want to make sure (example cell A1 is filled in before A2) In other words
they if A1 is not filled in you cant move to A2.

You need to apply this for each worksheet.
--
If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:


Hi Jacob,
The formula worked for one worksheet, but the other one already have a data
validation formula and it did not worked. Any other suggestion?
"Jacob Skaria" wrote:

To make sure A1 is filled before A2

1. Select A2

2. Enter the below formula in DataValidationCustom and uncheck Ignore blank
=$A$1<""

3. Enter an error message say "Cells A1 should be entered firts" in Data
ValidationError Alert....and click OK.

If this post helps click Yes
---------------
Jacob Skaria


"Link" wrote:

In a worksheet where you have to fill in information what formula to use if
you want to make sure (example cell A1 is filled in before A2) In other words
they if A1 is not filled in you cant move to A2.