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

 

[Yongle]

Right click on sheet tab \ view code \ paste code below in window on right
(which places the code in sheet module)
Then try entering values in sheet to see if it works as required

It applies in columns 1 to 8 (A to H)
- amend after testing

[COLOR=#000080]Goes in SHEET module[/COLOR]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > [COLOR=#ff0000]8[/COLOR] Or Target.CountLarge > 1 Then Exit Sub
    Dim cel As Range: Set cel = Cells(Target.Row, Columns.Count).End(xlToLeft)
    Application.EnableEvents = False
    If IsEmpty(cel.Value) Then cel.Activate Else cel.Offset(, 1).Activate
    Application.EnableEvents = True
End Sub

 

[holy_eti]

For a reason that I dont know, when I am selecting a cell that is empty in the column H, it refers me to the same line in columns S or R, depending of the line. Could you help with this one

 

[Yongle]

It would do that if Excel thinks the cell in column Q (or R) is not empty
VBA is simply looking for the last non-empty cell in the target row with this line

 Set cel = Cells(Target.Row, Columns.Count).End(xlToLeft)

 

[JoeMo]

You haven't provided enough information to allow a solution. Exactly what range do you want the "lock" capability applied to? Do you want the user to be able to edit cells that were filled previously?

Are you ok with just preventing the user from leaving the "lock" range if all cells in it are not filled?

 

[holy_eti]

I understood my error! Thanks

 

[holy_eti]

@JoeMo thats exactly what i want to do. For a certain range, i dont want my user to leave empty cells if the previous ones are filled. That range is on the "y" axis. So for a range made of multiple lines and one column, I want to tell VBA that if the line "x" is filled, then line under it must be filled. And if possible, my operator shouldnt be able to fill the lines under an empty line until that empty line is filled.

 

[JoeMo]

You need to be specific and answer the questions:

Exactly what range do you want the "lock" capability applied to? Do you want the user to be able to edit cells that were filled previously?

Are you ok with just preventing the user from leaving the "lock" range if all cells in it are not filled?

If you want to monitor cell by cell filling then you need to specify exactly what fill order you want the user constrained to follow. For example, if the range is A1:B3 do you want the sequence to be A1, A2, A3, B1, B2, B3 OR A1,B1,A2,B2 ..... OR what?

 

[holy_eti]

I am sorry for not being specific. I want my user to have the range "H7:G12" to be locked. And the fill order will be H7,H8,H9,H10,H11,H12,G7,G8,G9,G10,G11,G12. AS an exemple, If the user fill H7 and H8, skip H9 and tries to fill up H10, Then there will be a msgbox telling that the user must fill H9 before H10.

Is that good?
Thanks

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your data in G7:H12. This macro also takes into account if the user skips 2 or more cells.

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 > 7 Then
        Select Case Target.Column
            Case Is = 7
                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
            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