Stop user from closing workbook until conditions apply
Hi all,
I have a worksheet that requires specific cells to have data input in by the user before the workbook is closed. The problem is my users still manage NOT to complete all the required cells!!
What I'm looking for is some way of stopping them from closing/saving the workbook until the conditions are met.
I've come up with the following code so far but I'm looking for the code that will stop the workbook from being closed.
Any suggestions?? :help
[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Daily Centre Inputs").Range("D6,F6,C8:C18,I6:I18,A22:K22,A29,A36,H36").Value = "" Then
MsgBox "Incomplete fields. Please check your data ensuring any required cells are complete otherwise you will not be able to close or save the workbook"
'What do I need to code here to stop workbook from being closed????
End If
End Sub
[/VBA]
Cheers :hi:
Workbook Event BeforeClose but how can I save a blank template and exit?
Hi Jacob,
I realize this is a very old post but hoping I can still get a reply!
Your code works wonderfully, I was able to adapt it but it works so well I don't have a way around it and I need to save a template without the data. Is there a way to code in - make it do this except for me? or If I enter some word into a cell? The only thing I've figured out is to save it and force quit from the task manager.
Thank you in advance!
-Karyn (newbie to VBA, learning as I go!)
Quote:
Originally Posted by
Jacob Hilderbrand
You can try something like this. It will stop closing, as well as tell the user what cells need to be filled out, then it will select those cells.
[vba]
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
Set Rng1 = Sheets("Daily Centre Inputs").Range("D6,F6,C8:C18,I6:I18,A22:K22,A29,A36,H36")
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
[/vba]