VBA Function to restrict save option after Before Close Macro [SOLVED]

[marcwlms]

Hi I amm new to VBA and have managed to patch something together from various excel forums.


I have a case management spread sheet which has a number of rows. The first 9 are mandatory when the spread sheet is 1st completed. Users can subsequently complete the rest of their row as the issue they are dealing with progresses.


I have used the following BeforeClose VBA so it warns them if they haven’t completed the 9 cells we require to but it then comes up with standard excel close dialogue box.

[pre]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rngCell As Range, strBlanks As String

Application.ScreenUpdating = False

strBlanks = vbNullString
For Each rngCell In Worksheets("Sheet1").Range("a2:a2000").Cells
If Len(Trim(rngCell.Value)) > 0 Then
If WorksheetFunction.CountA(rngCell.Offset(0, 1).Resize(1, 9)) < 9 Then
strBlanks = strBlanks & IIf(Len(strBlanks) > 0, ",", "") & _
Replace(rngCell.Offset(0, 1).Resize(1, 9).SpecialCells(xlCellTypeBlanks).Address, "$", "")
End If
End If
Next

If Not strBlanks = vbNullString Then
MsgBox "When initially logging a case entries are mandatory in columns A - J. Please complete entries in the following columns" & vbCrLf & vbCrLf & strBlanks
Cancel = True
Exit Sub
End If
End Sub

[/pre]
The issue is they could ignore and save their changes and therefore mandatory information would be left blank also the next user who may well complete their row correctly will get message relating to someone else’s data which they can see.


Is it possible to stop this and/or apply this to a BeforeSave VBA code at the same time so it works in that scenario also that if they try and save before closing they will get the same message

 

[NARAYANK991]

Hi Marc ,


You can have the same code in the BeforeSave event procedure.


The key statement in both these event procedures will be :


Cancel = True


which tells Excel to abort the Close / Save procedure.


Hence if the mandatory fields have not been filled up , and the two event procedures put Cancel to True , Excel will leave the Workbook Open / Unsaved.


Narayan

 

[marcwlms]

Narayan,


Thanks for this.


so do i just paste in the same vba and change beforeClose to beforeSave or do i change it before pasting it in?


I tried doign this but it didnt seem to like it very much


M

 

[NARAYANK991]

Hi Marc ,


The problem is the definition of the two procedures ; the Workbook_BeforeClose is as follows :

[pre]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub
while the Workbook_BeforeSave is as follows :

[pre][code]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

[/pre]
What you need to do is insert the BeforeSave procedure in your VBE , and then copy + paste the following section of code in it.

Dim rngCell As Range, strBlanks As String

Application.ScreenUpdating = False

strBlanks = vbNullString
For Each rngCell In Worksheets("Sheet1&#34.Range("a2:a2000&#34.Cells
If Len(Trim(rngCell.Value)) > 0 Then
If WorksheetFunction.CountA(rngCell.Offset(0, 1).Resize(1, 9)) < 9 Then
strBlanks = strBlanks & IIf(Len(strBlanks) > 0, ",", "&#34 & _
Replace(rngCell.Offset(0, 1).Resize(1, 9).SpecialCells(xlCellTypeBlanks).Address, "$", "&#34
End If
End If
Next

If Not strBlanks = vbNullString Then
MsgBox "When initially logging a case entries are mandatory in columns A - J. Please complete entries in the following columns" & vbCrLf & vbCrLf & strBlanks
Cancel = True
Exit Sub
End If[/code][/pre]
Narayan

 

[marcwlms]

Thanks again for your help!