VBA code help needed

Status
Not open for further replies.

sneel3

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
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

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

That's where the code goes.

Smitty
 
Status
Not open for further replies.

Forum statistics

Threads
1,141,488
Messages
5,706,665
Members
421,460
Latest member
MTME

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
Top