Stop users leaving cells blank

[Lozzaw]

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

 

[JLGWhiz]

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

 

[Gord Dibben]

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

 

[fisch4bill]

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]

I posted BeforeClose code.

Do you not see it?


Gord

 

[fisch4bill]

I do now - sorry

I posted BeforeClose code.

Do you not see it?


Gord



.

 

[Gord Dibben]

Is OK

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


Gord

 

[Lozzaw]

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]

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