VBA code help needed

[sneel3]

I need to warn users that there are blank cells that need to be filled in. I would like to do this when they try to save (or save as) the file. How do I create a pop-up that tells them that "There are empty cells that need to be filled in"?

The following was suggested:

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

Dim ChkRng As Range, c As Range

With Sheets("Sheet1") 'Change to the sheet of choice
Set ChkRng = .Range("A1:C10") 'Change to the range of choice
For Each c In ChkRng
If IsEmpty(c) Then
Cancel = True
Sheets("Sheet1").Select
c.Select
MsgBox ("All cells in the range " & ChkRng.Address(0, 0) & " must have an entry before saving."), , "Save cancelled"
Exit For
End If
Next c
End With
End Sub

I changed "A1:C10" to "M11:N11" to test the code
I changed "Sheet1" to "Form"

 

[Smitty]

So doesn't it work for you?

Smitty

 

[Doug.T]

The macro works.
Move it to the ThisWorkbook module if it is not already there.
Are Macros Enabled and is Macro-Security set to Medium?

 

[sneel3]

It does not work. I must be doing something stupid...or not doing something I am supposed to. Other than inserting a module and pasting in this code and saving it, what else do I need to do? Please be very, very specific, I amapparently really slow today.

Does anything else (no matter how small) need to be changed in the vba? here is what mine looks like right now:

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

  Dim ChkRng As Range, c As Range 

    With Sheets("Form") 'Change to the sheet of choice 
      Set ChkRng = .Range("M11:N11") 'Change to the range of choice 
        For Each c In ChkRng 
          If IsEmpty(c) Then 
            Cancel = True 
            Sheets("Form").Select 
            c.Select 
            MsgBox ("All cells in the range " & ChkRng.Address(0, 0) & " must have an entry before saving."), , "Save cancelled" 
            Exit For 
          End If 
        Next c 
    End With 
End Sub

When I press save (or save as) nothing happens and the cells are empty. I entered 0 in as well and nothing happened.


HELP!!!

EDIT: Added code tags - Smitty

 

[Smitty]

As Doug pointed out, the code needs to go in the ThisWorkbook module. It will not fire in a general module.

Other than that it looks good.

Smitty

 

[sneel3]

It is in the following place:

- VBAProject (SubaccrualTemplatev2.xls)
- Microsoft Excel Objects
Sheet1 (Form)
Sheet2 (Instructions)
Sheet3 (DataInput)
- Modules
Module1
Module2
Module3
Module4
Module5
Module6

 

[Smitty]

Under microsoft Excel Objects there will be a module called ThisWorkbook.

That's where the code goes.

Smitty

 

[sneel3]

OH! I may have to change my name to "slowkid" in the forum.

 

[Smitty]

Duplicate Post.

It's best to keep relevant topics in the same post.

Smitty