Locking cells If previous cell isnt filled

[holy_eti]

Hello, I would like to know if there is anyway to "lock" a cell if the previous cell isnt filled. In other words, I have to fill a file with multiple cells. If I accidently forget to fill one cell and pass it up, I want Excel/VBA to tell me that I have forgot to fill a cell. I have a small idea but I dont really know how to properly code it on VBA. My idea is that if the cell "A1" is filled, than the cell "A2" must be filled, so VBA must tell me if I went over cell A2. I can do a small code but Excel keep thinking that when I select a case, the message Box of my empty cell pops before i can write anything. I want it to pop if I go on an empty cell, than leave it without writting anything in it.

THank you

 

[Rick Rothstein]

Copy and paste this macro into the worksheet code module.

Your code allows someone to enter text in cell G7 when cell H12 is blank.

 

[Rick Rothstein]

Copy and paste this macro into the worksheet code module.

Your code allows someone to enter text in cell G7 when cell H12 is blank.

I think this modification to your code fixes the problem...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G7:H12")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim fEmptyRow As Long
    If Target.Row [B][COLOR="#FF0000"]>=[/COLOR][/B] 7 Then
        Select Case Target.Column
            Case Is = 7
[B][COLOR="#FF0000"]                If Range("H12").Value = "" Then
                    MsgBox ("Please enter data in cell H12.")
                    Target.ClearContents
                    Range("H12").Select
                    Application.EnableEvents = True
                    Exit Sub
                Else
[/COLOR][/B]                    fEmptyRow = Range("G7:G12").Cells.SpecialCells(xlCellTypeBlanks).Row
                    If Target.Row > fEmptyRow Then
                        MsgBox ("Please enter data in cell G" & fEmptyRow & ".")
                        Target.ClearContents
                        Range("G" & fEmptyRow).Select
                        Application.EnableEvents = True
                        Exit Sub
                    End If
 [B][COLOR="#FF0000"]               End If
[/COLOR][/B]            Case Is = 8
                fEmptyRow = Range("H7:H12").Cells.SpecialCells(xlCellTypeBlanks).Row
                If Target.Row > fEmptyRow Then
                    MsgBox ("Please enter data in cell H" & fEmptyRow & ".")
                    Target.ClearContents
                    Range("H" & fEmptyRow).Select
                    Application.EnableEvents = True
                    Exit Sub
                End If
        End Select
    End If
    Application.EnableEvents = True
End Sub

 

[mumps]

Rick, thank you for picking that up.

 

[holy_eti]

@mumps This works pretty good for me!! If you have any time could you explain to me what the Line Case Is is doing? I am curious
Thanks

 

[mumps]

Select Case Target.Column

tells the macro to look at the column number of the cell that has been selected for data entry. If you select a cell in column G the column number would be 7, if column H, the column number would be 8. Then

 Case Is = 7

is the same as

If Target.Column = 7

I hope this helps.

 

[holy_eti]

Thanks I've figured out while waiting for your answer! @RickRothstein Thank you for the modification!!!

 

[mumps]

You are very welcome. Glad we could help.