Mandatory Cells in Excel 2007

[I heart Excel]

Hello,

I am trying to make cells within Excel 2007 mandatory before the user can save.

I have had a look online and so far found -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("B5").Value = "" Then
MsgBox "Please enter value in B5"
Cancel = True 'cancels the save event
End If
End Sub

This works fine, but I have a few additional things that I have looked around for but can't seem to get to work! I need to be able to save the document after putting this into VBA, but can't as I keep getting the error about the mandatory cells! Also I have random cells (a range) that need to be filled out. not just one.

Can anyone help?

Thanks

 

[JoeMo]

You can save the workbook from the VBA editor - under File menu on the editor. What do you mean by: "Also I have random cells (a range) that need to be filled out. not just one."? If you know the cells you want the user to put values in, list them and maybe I can help.

 

[I heart Excel]

Hello,

I need cells -

B2
C2
D2
E2
F2
G2
H2
J2
K2
M2

Thanks for your help,

 

[JoeMo]

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim rng As Range, ct As Integer, sAdr As String
ct = 0
sAdr = ""
With Sheets("Sheet1")
    Set rng = .Range("B2:H2,J2:K2,M2")
End With
For Each c In rng
    If IsEmpty(c) Then
        ct = ct + 1
        sAdr = sAdr & ", " & c.Address(0, 0)
    End If
Next c
If ct > 0 Then
    MsgBox "Please enter value(s) in cell(s): " & Right(sAdr, Len(sAdr) - 1)
    Cancel = True 'cancels the save event
End If
End Sub

 

[I heart Excel]

Thanks for this, it did work. I still can't save the file after inputting this in VBA, and I followed your instructions! The error message just comes up about the mandatory fields.

Thanks for all your help

 

[JoeMo]

One thing that you can do is to have the cells you want the user to fill in be cleared when the workbook is opened. This would allow you to place values in those cells so you can save the workbook with the code in it. Then when the workbook is next opened those cells would be cleared. If that will work for you, add the code below, then fill the cells, then save and close the workbook. The code below goes into the ThisWorkbook module too.

Private Sub Workbook_Open()
Dim rng As Range
With Sheets("Sheet1")
    Set rng = .Range("B2:H2,J2:K2,M2")
End With
rng.ClearContents
End Sub

 

[I heart Excel]

Not sure what I am doing wrong!! But I still can't get it to work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I had a look around and have now found the following, but I need it tweaking.<o></o>
<o></o>
The form I have is a request form for new items to be added to our internal purchasing system. The buyers are asked to fill out numerous cells and then they save. The next time someone opens the form they fill out the row below and the cells should again be mandatory.<o></o>
<o></o>
I have entered a formula into the end unused cell - <o></o>
<o></o>
=IF(AND(B2="",C2="",E2=""),"Yes","No")<o></o>
<o></o>
I then have entered the following into VBA Workbook - <o></o>
<o></o>
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)<o></o>
If Sheets("Sheet1").Range("N2").Value = "Yes" Then
MsgBox "Please fill Mandatory Fields"
Cancel = True 'cancels the save event
End If
End Sub<o></o>
<o></o>
Two problems I have - <o></o>
<o></o>
If one of the 3 cells has data in the result formula equals No and therefore can be saved without the other 2 mandatory cells being filled in.<o></o>
<o></o>
As for the range I need this to drag down the rows. This is because if one buyer fills out a row, I don't want them to then not be able to save as the other rows haven't.<o></o>
<o></o>
Thanks for all your help <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\aliclock\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>