S
Susan Ramlet
I just wanted to say "thanks" to Chip for a bit of code he posted in this
newsgroup in March of 2000 that I found archived on Google. It's a
BeforeClose macro that checks for required values. It really helped me
today--I was able to use much of it for an internal project, and since I'm
not much of a coder, it saved me a boatload of research hours to figure out
how do it.
Here's his original message. I modified it to be a BeforeSave procedure and
I'm still working on having it iterate through a series of worksheets, but
it's basically just what I needed. Thank you!
Susan Ramlet
Use the BeforeClose event procedure to
check the cells when the user tries to close the workbook. Put the
following code in the ThisWorkbook code module, not a standard code module.
Change the specification of ReqFields to the cells that you need to check.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ReqFields As Range
Dim Rng As Range
Dim Res As Long
With Worksheets("Sheet1")
Set ReqFields = .Range("A1,B2,C3,D4,E5")
End With
For Each Rng In ReqFields
If Rng.Value = "" Then
Rng.Parent.Activate
Rng.Select
Res = MsgBox("You have not included a required field." & vbCrLf & _
"Are you sure you want to close the workbook?", _
vbYesNo + vbDefaultButton2)
If Res = vbNo Then
Cancel = True
End If
Exit Sub
End If
Next Rng
End Sub
newsgroup in March of 2000 that I found archived on Google. It's a
BeforeClose macro that checks for required values. It really helped me
today--I was able to use much of it for an internal project, and since I'm
not much of a coder, it saved me a boatload of research hours to figure out
how do it.
Here's his original message. I modified it to be a BeforeSave procedure and
I'm still working on having it iterate through a series of worksheets, but
it's basically just what I needed. Thank you!
Susan Ramlet
Use the BeforeClose event procedure to
check the cells when the user tries to close the workbook. Put the
following code in the ThisWorkbook code module, not a standard code module.
Change the specification of ReqFields to the cells that you need to check.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ReqFields As Range
Dim Rng As Range
Dim Res As Long
With Worksheets("Sheet1")
Set ReqFields = .Range("A1,B2,C3,D4,E5")
End With
For Each Rng In ReqFields
If Rng.Value = "" Then
Rng.Parent.Activate
Rng.Select
Res = MsgBox("You have not included a required field." & vbCrLf & _
"Are you sure you want to close the workbook?", _
vbYesNo + vbDefaultButton2)
If Res = vbNo Then
Cancel = True
End If
Exit Sub
End If
Next Rng
End Sub