Entering information

  • Thread starter Thread starter Link
  • Start date Start date
L

Link

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 Data>Validation>Custom and uncheck Ignore blank
=$A$1<>""

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

If this post helps click Yes
 
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?
 
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
 
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
 
This is what I need the information to apply only certain cells; not every
cells. Is this possible?
 
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
 
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)
 
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"
 
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.
 
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.
 
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.
 
Back
Top