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

Put the code in the worksheet code module for the sheet where you want it to
run.

Private Sub Worksheet_Change(By Val Target As Range)
If Target.Column = 1 Then
For Each c In Range("A1:A4")
If IsEmpty(c.Value) Then
MsgBox "You must fill in cell " & c.Address
Application.Goto c
Cancel = True
Exit For
End If
Next cell
End If
End Sub






"Lozzaw" wrote in message
...
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

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

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


.

I posted BeforeClose code.

Do you not see it?


Gord

On Fri, 2 Apr 2010 05:40:01 -0700, fisch4bill
wrote:

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


.

I do now - sorry

"Gord Dibben" wrote:

I posted BeforeClose code.

Do you not see it?


Gord

On Fri, 2 Apr 2010 05:40:01 -0700, fisch4bill
wrote:

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


.


.

Is OK

You don't want to know some of the details I have missed in these groups.


Gord

On Fri, 2 Apr 2010 12:55:01 -0700, fisch4bill
wrote:

I do now - sorry

"Gord Dibben" wrote:

I posted BeforeClose code.

Do you not see it?


Gord

On Fri, 2 Apr 2010 05:40:01 -0700, fisch4bill
wrote:

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


.


.

Hi

Thanks for your replies, sorry I haven't replied sooner, for some reason I
didn't get notifications.

Anyway, JLGWhiz, I tried your code, but can't get it to work. I don't get
any error message when saving the file with blank cells.

fisch4bill I appreciate your idea, but I'm afraid it still doessn't give me
the solution I need. Users will just ignore anything telling them to do
things they don't want to!

My conclusion is there is no way of stopping users leave empty cells in
Excel. Surprising.

Cheers

"Gord Dibben" wrote:

Is OK

You don't want to know some of the details I have missed in these groups.


Gord

On Fri, 2 Apr 2010 12:55:01 -0700, fisch4bill
wrote:

I do now - sorry

"Gord Dibben" wrote:

I posted BeforeClose code.

Do you not see it?


Gord

On Fri, 2 Apr 2010 05:40:01 -0700, fisch4bill
wrote:

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


.


.


.

Not true.

But requires VBA event code to prevent saving or closing the workbook if
cells are not filled and users must enable macros.


Gord Dibben MS Excel MVP

On Tue, 6 Apr 2010 05:15:01 -0700, Lozzaw
wrote:

My conclusion is there is no way of stopping users leave empty cells in
Excel. Surprising.