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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,362
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,640
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,362
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,102,630
Messages
5,488,000
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top