Excel VBA to prevent Excel file before closing & saving

Topic

New Reply

Hi,

I am trying to prevent the excel file to have specific cells filled prior closing & saving it. Since I am new to VBA can someone suggest me or write a VBA for me in this case ?

Reply | Quote

Replies

Mohan,

Place this code in the ThisWorkbook Module. Change the cells to which ever you want cleared. You can have as many as you want. You could also apply a named range instead (ex, Scores is cells A1, B1, C1). When you attempt to save, the cells will be cleared prior.

HTH,
Maud

Individual cells:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    [a1] = “”
    [b1] = “”
    [c1] = “”
End Sub

Named Range:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    [Scores].ClearContents
End Sub

From Excel, press Alt-F11 then paste the code as the image below describes.

38490-saveclose

Reply | Quote

Put these two macros in the THISWORKBOOK module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Len(Application.Trim(Sheets(“sheet1”).Range(“a1”))) < 1 Then
MsgBox "fill cells"
Cancel = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(Application.Trim(Sheets("sheet1").Range("a1"))) < 1 Then
MsgBox "fill cells"
Cancel = True
End If
End Sub

Reply | Quote

I believe the BeforeClose event is not necessary as the Before save will always clear the cells before closing unless of course you do not want to save (which they will then be cleared on closing). I also think the OP wants to prevent the cells from being filled

Reply | Quote

Hey Y’all,

Depending on the workbook requirements another option would be to just unlock all the cells, lock the ones you don’t want filled, then protect the workbook. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Would something like this be OK?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Target.Value = “”
End If
Application.EnableEvents = True
End Sub

Good Luck
Alexandra

Reply | Quote