Code Needed, Excel File not close untill fill the required data

[inbp]

Hi Brilliants!


I have two issues with my file:


1.i have used chandoo's checklist in my file, but at the time of opening its shows an error which repair the file and save as new file.


2. i want a code that someone can not close the file untill fill the required fields.


The file i attached here contains 3 sheets


1st is checklist which i taken from chandoo's post

2nd main sheet in which required data must filled in pink cells

3rd error screen shot which appeared at the time of opening file


Below is the file link


http://www.2shared.com/file/uou1evGq/Report__Field_.html


will be grateful


Thanks & Regards

Muhammad Shakeel

 

[inbp]

is there no solution?

 

[inbp]

need help and guidance

 

[anupamtiwari05]

Hi inbp,


Please check the below link.


http://www.vbaexpress.com/forum/showthread.php?t=693


Hope this will help you out.


Thanks & Regards,

Anupam

 

[inbp]

Hi Anupam


i try your link but not working

 

[inbp]

Help plzzzzzzzzzz


Friends i have use this in module


but its not working


Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error GoTo ErrorHandler

If IsEmpty(Sheet1.Range(Sheet1.Range("A1"))) Then

MsgBox "Please fill in cell " & Sheet1.Range("A1") & " before closing."

Cancel = True

End If

Exit Sub

ErrorHandler:

MsgBox "Cell has invalid range reference."

Cancel = True

End Sub

 

[bobhc]

Good day inbp


I have just run this code to check it and it does as you need, you will need to change the sheet name and the cell references to suit your work sheet.

Just copy and paste in to the sheet not a module.

[pre]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Rng1 As Range
Dim Rng2 As Range
Dim Prompt As String
Dim Cell As Range
Dim AllowClose As Boolean

AllowClose = True

'Sheet name and cell references, change as required.
Set Rng1 = Sheets("Daily Centre Inputs").Range("D6,F6,C8:C18,I6:I18,A22:K22,A29,A36,H36")
'This is what will show on screen if the correct cells are not filled with data
Prompt = "Please check your data ensuring all required " & _
"cells are complete." & vbCrLf & "you will not be able " & _
"to close or save the workbook until the form has been filled " & _
"out completely. " & vbCrLf & vbCrLf & _
"The following cells are incomplete:" & vbCrLf & vbCrLf

For Each Cell In Rng1
If Cell.Value = vbNullString Then
Prompt = Prompt & Cell.Address(False, False) & vbCrLf
AllowClose = False
If Rng2 Is Nothing Then
Set Rng2 = Cell
Else
Set Rng2 = Union(Rng2, Cell)
End If
End If
Next
If AllowClose Then
Else
MsgBox Prompt, vbCritical, "Incomplete Data"
Cancel = True
Rng2.Select
End If

End Sub

[/pre]