Multiple Worksheet_Deactivates

Chewyhairball

Active Member
Joined
Nov 30, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Yes. That’s correct. I quite like your bit of code. That might be useful ... thanks
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top