Multiple Worksheet_Deactivates

[Chewyhairball]

Hi Folks

I have the following code that basically does not allow someone to leave the sheet they are on unless all required field are complete. It works perfect for my needs

My question is is it possible to have more than one worksheet_deactivate say for Range A2 with a different message. These would be two seperate critera and not and/or statments.

thanks

Rory

Private Sub Worksheet_deactivate()

Set target = Range("A1")
If target.Value > "0" Then
MsgBox "You must click the Inform AWO button above before you can save and leave this sheet", , "Input Required"
Sheet2.Activate
End If

End Sub

 

[Yongle]

I generally use data validation to check most cell values at input
- so although message is "You must enter a number in A1" the check below is for an empty cell
- A2 may be a dropdown where every value start "Emp..." - so checking for "Emp" is to ensure something has been selected

You can do this type of thing
- test key cells as required
- assign a message to each one
- avoid repeating too many lines of code (there is only one message box, by using boolean variable the worksheet is only reactivated once etc)
- send user back to the cell containing incorrect or missing value

Private Sub Worksheet_deactivate()
    Dim b As Boolean, msg As String, addr As String

    If IsEmpty(Range("A1")) Then
        msg = "You must enter a number in A1"
        b = True
        addr = "A1"
    End If
    
    If Left(Range("A2").Value, 3) <> "Emp" Then
        msg = "You must enter select employee in A2"
        b = True
        addr = "A2"
    End If

    If b = True Then
        Me.Activate
        Range(addr).Select
    End If
    If msg <> "" Then MsgBox msg, , "Input Required"
End Sub

 

[mikerickson]

The ThisWorkbook code module has a Workbook_SheetDeactivate event that fires when any sheet is deactivated

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox "You just left sheet " & sh.Name
End Sub

 

[Yongle]

Following on from @mikerickson valid comment..
@Chewyhairball
My understanding is that
- you are only concerned about one worksheet
- you want to provide different messages to the user to tell him which area of that sheet is causing the problem

Please confirm
thanks

 

[Chewyhairball]

Yes. That’s correct. I quite like your bit of code. That might be useful ... thanks