How to save macro when code is prevent saving if there's blank cells ?

[gilver]

as above ? Sort of new to VBA coding, so pardon my newb questions

Have created an macro in "thisworkbook" that prevents user from saving the file should there be any blank files.

How should i save this macro in order for the file to be sent then ? File will be sent to users for them to enter their data.

TIA

[cytop]

Put a breakpoint in the procedure and use Debug/Set next statement to move the next line to execute to the end of the procedure.

Use the Immediate Window to set event handling off

Please Login or Register  to view this content.

(Don't forget to turn on again after saving)

Just a couple of ways...

[gilver]

thank you for your reply

was able to get the breakpoint down, but debug/next step was greyed out. here's the code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Zell As Range
Dim flag As Boolean
flag = False
If Cells(1, 1) = "" Then flag = True
For Each Zell In Range("J4:P233")
If Zell = "" Then
flag = True
Exit For
End If
Next Zell


Cancel = flag
If flag Then MsgBox "Please fill in all the blanks"
End Sub

[cytop]

I've no idea why the button would be greyed.

Here's another way.

Add a module (or use an existing module) to declare a public Variable

Please Login or Register  to view this content.

When declared, a boolean will have a default value of False.

Then check the value in the BeforeSave event. If the default value of False then allow the empty cell check to proceed, but if true then simply exit. To allow you to save you set NOCHECK to True in the immediate window.

The BeforeSave event was also rewritten a little. Why loop 1400 or so cells when you can check with 1 call to a native function. Also, as this is a Workbook level event, you need to specify which sheet the check applies to, otherwise the active sheet will be used.

Please Login or Register  to view this content.

[gilver]

you are a lifesaver m8. thank you!