Mandatory Cells before leaving sheet

[Leis_B]

Hi! Apologies if this has been posted elsewhere, I just can't seem to find anything that suits my purpose.

What I need to do, is check that a range of cells have a value entered before they can move off the sheet, using the button (I realise they can just move between sheets using the tabs). I've come up with this and it works, but when testing it and going back and removing the values, it won't activate the msgbox. What am I doing wrong?

Sub Mandatory1()
With Sheets("FORM_SCHEDNEWHIRE")
If IsEmpty(.Range("F5,F7,F9,F11,F13,F15,F17,F19,F21,F23,F25,F27,F31,F33,F35,F37")) Then
MsgBox "Please ensure you have answered all of the questions before moving on.", vbCritical
Application.Goto .Range("F5,F7,F9,F11,F13,F15,F17,F19,F21,F23,F25,F27,F31,F33,F35,F37")
Else
Sheets("FORM_BANK_SUPER").Select
Range("F6:I6").Activate
End If
End With
End Sub

Thanks in advance.

 

[JoeMo]

Why not use an event macro to make things automatic? Here's one you can try. This code goes in the sheet - see instructions below the code for installing it.

Private Sub Worksheet_Deactivate()
Dim c As Range, Unfilled As String
For Each c In Range("F5,F7,F9,F11,F13,F15,F17,F19,F21,F23,F25,F27,F31,F33,F35,F37")
    If IsEmpty(c) Then Unfilled = Unfilled & ", " & c.Address(0, 0)
Next c
If Unfilled <> "" Then
    MsgBox "Please answer the questions related to cells: " & Unfilled & " before leaving this sheet"
    Me.Select
Else
    Sheets("FORM_BANK_SUPER").Select
    Activesheet.Range("F6:I6").Activate
End If
End Sub

To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[Leis_B]

That is awesome!!! Thank you so much JoMo!! Does exactly what I want it to!!

 

[Rahulwork]

Hello JoeMo,

Sorry i m jumping in between but i need your urgent help on below query. Please help

https://www.mrexcel.com/forum/excel-questions/1025751-arrow-keys-combo-box-drop-down.html

 

[JoeMo]

That is awesome!!! Thank you so much JoMo!! Does exactly what I want it to!!

You are welcome - thanks for the reply.

 

[Rahulwork]

@JoeMo Please assist me too. here is my query:

https://www.mrexcel.com/forum/excel-questions/1025370-searchable-drop-down.html

 

[Leis_B]

You are welcome - thanks for the reply.

Hi JoeMo,

Thanks again for your help with this, I was hoping to impose on you a little further.

The coding does exactly what I want it to, however I need to send the sheet out empty in the first place, but I am now on this perpetual loop. Is there a way that I can save it "empty", so it is ok to send out to users? Was thinking that it could look at a different cell that I could have hidden or something that would "trigger" the mandatory cell coding? I just have no idea where to start on that...

Thanks in advance.

 

[JoeMo]

Clear whatever contents you want on the sheet. Then, without leaving the sheet, save the workbook.

 

[Leis_B]

Thanks JoeMo! Unfortunately, I have multiple sheets in my workbook that I have replicated the code with.

Any ideas?

 

[JoeMo]

Give me the sheet names and the range for each sheet you want to clear when the user opens the workbook.