Only allow worksheet change if all cells in my range are empty

[danbates]

Hi,

Does anyone have a VBA code that stops a user from switching between worksheets in my workbook if columns A, B, E, F & G on the active row are not all blank?

Thanks

Dan

 

[danbates]

I have managed to put this code together and it works, but only for row 485.

Private Sub Worksheet_Deactivate()  
If ThisWorkbook.Sheets("CHANGEOVER FORM").Range("A485").Value > 0 _
Or ThisWorkbook.Sheets("CHANGEOVER FORM").Range("B485").Value > 0 _
Or ThisWorkbook.Sheets("CHANGEOVER FORM").Range("E485").Value > 0 _
Or ThisWorkbook.Sheets("CHANGEOVER FORM").Range("F485").Value > 0 _
Or ThisWorkbook.Sheets("CHANGEOVER FORM").Range("G485").Value > 0 Then


MsgBox "Please either complete the last changeover or delete it"


Me.Activate


End If


End Sub

I really need some help to change the cell range from A485 to A, B485 to B and so because obviously after the next changeover this code becomes invalid.

Any help would be much appreciated.

Thanks

Dan

 

[igold]

Hi,

Does this get you any closer...

Private Sub Worksheet_Deactivate()


    Dim wsNew As String
    Dim ws As Worksheet: Set ws = Worksheets("CHANGEOVER FORM")
    Dim aCell As Long, i As Long
    Dim aRow As Range


    Application.ScreenUpdating = False
    wsNew = ActiveSheet.Name
    ws.Activate
    aCell = ActiveCell.Row
    Set aRow = ws.Range("A" & aCell & ":G" & aCell)
    For i = 1 To 7
        If Not aRow.Value2(1, i) = "" Then
            GoTo ComeBack
        End If
    Next
    Worksheets(wsNew).Activate
    Application.ScreenUpdating = True
    Exit Sub
ComeBack:
    MsgBox "Please either complete the last changeover or delete it"
    Worksheets("CHANGEOVER FORM").Activate
    Application.ScreenUpdating = True
    
End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Deactivate()
   Dim i As Long
   For i = 485 To Range("A" & Rows.Count).End(xlUp).Row
      If Application.CountA(Me.Cells(i, "A").Resize(, 7)) <> 7 Then
         MsgBox "Please either complete the last changeover or delete it"
         Me.Activate
         Exit Sub
      End If
   Next i
End Sub

 

[igold]

Errant post. Sorry.

 

[Fluff]

I've done the opposite of what the OP asked for, in that it checks all rows from 485 downwards have been filled in.
Otherwise somebody could partially fill-in a number of rows & they would be allowed to leave the sheet as long as the active cell was on a blank row.

 

[igold]

@Fluff,

You are too quick (your response). I did not notice what the OP wrote in Post #2 at the bottom. I am kinda doing other stuff and stopping at my computer in between, so I just deleted the post, instead of sitting down and taking a second look.

Nothing good ever comes from rushing.

 

[kvsrinivasamurthy]

Try this code

Private Sub Worksheet_Deactivate()


Application.EnableEvents = False
Dim T As Long
For T = 1 To 7
If T = 3 Then T = 5
    If Cells(ActiveCell.Row, T) = "" Then
    MsgBox ("Columns A,B,E,F,G of active row should not be blank.")
    Me.Activate
    Application.EnableEvents = True
    Exit Sub
    End If


Next T
Application.EnableEvents = True
End Sub

 

[Fluff]

@kvsrinivasamurthy
Your code will pick-up the activecell from the sheet selected, not the sheet with the code.

 

[danbates]

Hi igold, Fluff & kvsrinivasamurthy

Thank you for all your replies.

After reading all your messages I have decided to use igold's code and it works just as I wanted.

Thank you again everyone for your help, it really is appreciated.

Regards

Dan