Gord,
Wouldn't also help to write a BeforeClose segment to make ALL the sheets
EXCEPT Dummy xlVeryHidden? If the other sheets are still visible when a user
neglects to enable macros, they can just select one of these sheets and
blithely continue to work and leave blank cells, in spite of the
chastisement. The code you suggest seems to remedy that if the user enables
macros; this DOES force them to enable macros. At this point only someone
reasonably conversant with VBA would be able to bypass enabling of macros;
and they'd have to do it deliberately.

"Gord Dibben" wrote:

You cannot force users to enable macros but you can chastise them if they
don't enable macros.

Insert a new worksheet. Name it "Dummy".

In large bold font on this worksheet type

"Macros have been disabled, rendering this workbook useless. Please close
and re-open with macros enabled"

Add this code to Thisworkbook module.

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVisible
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save 'optional
End Sub


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 07:20:02 -0700, Lozzaw
wrote:

Hi

I have found some code from another thread which stops users leaving cells
blank and displays a warning message when the user attempts to save the file:

Dim cell As Range
For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell

Now, this works fine in the file itself and when I save it as a .xlsm file
(I am using Excel 2007). However, the problem is that when you go to open the
file the macro is intially disabled, as explained by the Information Bar. For
the macro to work, it has to be activated by the user. So, if a user choose
not to activate the code, then they can just leave the cells blank. Is there
a way round this to force the macro to work?

Thanks


.