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.

 

[Leis_B]

Thanks heaps for this JoeMo

"FORM_SCHEDNEWHIRE" - cells: F9,F11,F13,F15,F17,F19,F23,F25,F27,F31,F33,F35,F37
"FORM_BANK_SUPER" - cells: F6,F8,F10
"FORM_TAX" cells: F8
"FORM_EXTRA" - cells E47,E49,E51,F53,E55,E57,E59,F61

 

[JoeMo]

This code is for a Thisworkbook module. It will clear the cells you listed on the worksheets youlisted when a user first opens the workbook. Note: I assumed for sheet "FORM_EXTRA" your F53 was a typo and replaced it with E53.

To install ThisWorkbook code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. 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).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets(Array("FORM_SCHEDNEWHIRE", "FORM_BANK_SUPER", "FORM_TAX", "FORM_EXTRA"))
    Select Case sht.Name
        Case "FORM_SCHEDNEWHIRE": sht.Range("F5,F7,F9,F11,F13,F15,F17,F19,F21,F23,F25,F27,F31,F33,F35,F37").ClearContents
        Case "FORM_BANK_SUPER": sht.Range("F6,F8,F10").ClearContents
        Case "FORM_TAX": sht.Range("F8").ClearContents
        Case "FORM_EXTRA": sht.Range("E47,E49,E51,E53,E55,E57,E59,F61").ClearContents
    End Select
Next sht
Application.ScreenUpdating = True
End Sub

 

[Leis_B]

Thanks Joe. The cells I have are merged cells, so the coding you sent isn't working. (my apologies, I probably should have mentioned this before).

I was thinking that can I have the coding in each sheet looking at a cell and inserting an if statement for any value in that cell, that would then trigger the mandatory cell code?

But have no idea where or how i'd start that....

Thanks again for all your assistance, I really appreciate it.

 

[JoeMo]

Thanks Joe. The cells I have are merged cells, so the coding you sent isn't working. (my apologies, I probably should have mentioned this before).

I was thinking that can I have the coding in each sheet looking at a cell and inserting an if statement for any value in that cell, that would then trigger the mandatory cell code?

But have no idea where or how i'd start that....

Thanks again for all your assistance, I really appreciate it.

Did you try, in the code from post #12 , changing the addresses you gave me in post #11 to the merged cell addresses?

 

[Leis_B]

Thanks for all your help with this JoeMo!

I've worked it out.

 

[JoeMo]

Thanks for all your help with this JoeMo!

I've worked it out.

You are welcome - thanks for the reply.