Multiple Worksheet_Deactivates

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
62
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,319
Office Version
365
Platform
Windows
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,619
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,319
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,995
Messages
5,484,082
Members
407,428
Latest member
keongymonopoly

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top