Popup Error Messages For incompleet Sheet

[stokie21]

Hi Guys, Is there a way I can make an error message popup if cells are left empty.

As an example Say cell A2 needed a name to be entered and it was missed, as i press the save button can i have a message that says " you have not filled in cell A2 " ect ect

I have made a sheet and staff are missing silly things like yes no answers!!

Thanks

 

[VoG]

Try like this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("A2").Value = "" Then
    Cancel = True
    MsgBox "You must complete A2", vbExclamation, "NOT SAVED"
End If
End Sub

 

[stokie21]

I could not get that one working, I have quite a few merged cells I am not sure if it makes a difference or not

I need this for quite alot of cells Here is a screen shot. Its every box that has the black marker around it including the yellow boxes

If you can explain how i add to i will enter them myself

Thanks For your Help

 

[VoG]

Try like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Range("C11,H11,H13:H14,C13,C16,C18,C20,D23,D25,I25,C27,F27,I27,D29,I29,D31,C33,I33")) < 19 Then
    Cancel = True
    MsgBox "You must complete all fields", vbExclamation, "NOT SAVED"
End If
End Sub

 

[stokie21]

still can not get it to work! i enter the code save it close the sheet, reopen it then if i enter a few bits in and close it then it closes no warnings!!

[URL="http://dl.dropbox.com/u/34668963/Credit%20Card%20Payments%20222.xlsm"]http://dl.dropbox.com/u/34668963/Credit%20Card%20Payments%20222.xlsm[/URL][URL="http://dl.dropbox.com/u/34668963/Credit%20Card%20Payments%20222.xlsm"]
[/URL]

here is a copy The last cell that needs the code is C33, I dont need it in the offstop select box or below!


Thanks

 

[stokie21]

Agh, I think i entered it on the Sheet and not the Workbook!! Will check now

 

[wigi]

What is Application.EnableEvents set to at the moment?

 

[VoG]

The code has to go in the ThisWorkbook module, not a regular module.

The merged cells might be an issue. Maybe try http://www.mrexcel.com/forum/showthread.php?t=335123

 

[stokie21]

its working fine now, But it needs a tweek!!

I have entered all the info that i want,and checked the numbers match the cells and they do. The sheet is still saying to enter all info,here are the correct cells and code:

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Range("C11,H11,C13,H14,C16,C18,C20,D23,D25,I25,C27,F27,D29,I33")) < 19 Then
    Cancel = True
    MsgBox "You must complete all fields", vbExclamation, "NOT SAVED"
End If
End Sub

2nd problem say i open the sheet by mistake, as its on my desktop the sheet will not just close it says i need to enter the info!
I know thats its job but it needs a tweek!!

Can we tweek the code so that it will kick in as soon as something is entered in cell C11 ? if nothing is entered then the rule will not apply?

 

[VoG]

Try this: change Sheet1 to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Sheets("Sheet1").Range("C11,H11,H13:H14,C13,C16,C18,C20,D23,D25,I25,C27,F27,I27,D29,I29,D31,C33,I33")) < 19 Then
    Cancel = True
    MsgBox "You must complete all fields", vbExclamation, "NOT SAVED"
End If
End Sub