macro upon Save

[rexie]

Hi all,
Can someone please help me to write this macro Which shoudl activate always upon save of the Excel File. The macro should check A1:A100 (Range A1 to A100) and b1:b100(Range from B2 to B100), if there is any field in the range empty , the maco should tell that the range (specific) is empty so cannot save. This maco should activate, everytime, user Saves the Document.

Please help

thanks a lot

 

[AWithers]

You can use the beforesave event

go in to visual basic editor and paste this code into the code window and insert you macro code where shown

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'INSERT YOUR CODE HERE
End Sub

Hope this helps

Andy

 

[xlbo]

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim myRange As Range
Set myRange = ActiveSheet.Range("A1:B100&quot
For Each cel In myRange
If cel.Text = "" Then
MsgBox "Range " & cel.Address & " is empty - save failed"
Cancel = True
Exit For
Else
End If
Next
End Sub

HTH
Geoff

 

[rexie]

Thanks a Lot,
I know this is a stupid question, please could you tell me how to paste this code inot the editor, I tried pasting it inot the visual basic editor, nothing works,

please suggest

 

[rexie]

Thanks guys I figured it out and it works fine except for a slight modification.
There are 5 columns in the Excel sheet, and if A1 not empty or NOT NULL then D1 and E1 cannot be empty and the BeforeSave event should look only inot records where Column A is Not blank check only for those columns.

Please suggest

 

[xlbo]

Don't understand your logic - you wanted to check A AND B for nulls - now you want to check only A for entries and then test for D and E.....Please give the full logic flow that you wish to achieve
Rgds
Geoff

 

[rexie]

Thanks XLBO,

here is the full logic below:-

If Column A (When I say Column A, it should check Only for that cell, not for the whole Column A, for eg:- A1:B1 is called a cell)is not null then column C,D should be mandatory, if Column C or Column D is Blank then Error Message should popup.

Please revert back, I shall try to explain to my very best.

 

[xlbo]

Still not quite sure - If this is correct, please reply and I'll amend the code:
If a cell in col A OR col B has an entry, then D AND E must have an entry, else, don't save
If no entry in A AND B then don't save.
Basically, Only save if entries in all of A1:B100 and C1100

I'm slightly confused as to where column B fits into this...

Rgds
Geoff