Results 1 to 5 of 5

Thread: Multiple Worksheet_Deactivates

  1. #1
    Board Regular
    Join Date
    Nov 2017
    Posts
    54
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple Worksheet_Deactivates

    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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,868
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Multiple Worksheet_Deactivates

    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

    Code:
    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

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,578
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Multiple Worksheet_Deactivates

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

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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,868
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Multiple Worksheet_Deactivates

    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
    Last edited by Yongle; Jan 22nd, 2019 at 10:15 AM.

  5. #5
    Board Regular
    Join Date
    Nov 2017
    Posts
    54
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Worksheet_Deactivates

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •