VBA code help needed

Status
Not open for further replies.

sneel3

Active Member
Joined
Oct 9, 2002
Messages
334
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"
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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?
 
Upvote 0
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:
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Under microsoft Excel Objects there will be a module called ThisWorkbook.

That's where the code goes.

Smitty
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top