Message box upon saving excel file

Is it possible to do a validation when the save button is clicked?

Example, if cell A2 is blank, then when the save button is clicked, a message box with a message "This is a required field".

Hi

How about a before save event

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If IsEmpty(Sheets("Sheet1").Range("A2")) Then
    Sheets("Sheet1").Activate
    Range("A2").Select
    MsgBox "Fill in the required cell A2."
    Cancel = True
  End If
End Sub

HTH

rylo

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Sheets("Sheet1").Range("A2")) Then
Sheets("Sheet1").Activate
Range("A2").Select
MsgBox "Fill in the required cell A2."
Cancel = True
End If
End Sub

For this code, how am I suppose to modify it such a way that if B2 or any other column in B has value, A2 or any other column in A is required also?

Hi

How about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim ChkSH As Worksheet
  Set ChkSH = Sheets("Sheet1")
  With ChkSH
    For Each ce In .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
      If Not IsEmpty(ce) And IsEmpty(ce.Offset(0, -1)) Then
        ChkSH.Activate
        ce.Offset(0, -1).Select
        MsgBox "Fill in the required cell: " & ce.Offset(0, -1).Address
        Cancel = True
        Exit For
      End If
    Next ce
  End With
End Sub

It will check all the cells in sheet1 column B for the used range, and if there is something in column B and nothing in the equivalent column A cell, it will put up the message and stop the save process.

rylo

That is strange i still manage to save it even when one column of A is blank and B has value

Hi

Can you put up an example file so we can see what you have.


rylo

it will be helpful if u can solve this problem