Required Data in cell

[cmac]

Is there a way to require that a particular cell be populated prior to saving
an Excel file (one cell is text and one is a date field)? We are using a
spreadsheet to collect data from users that is later used to feed an Access
database and need to be sure that the source file has these two key cells
populated.

Thank you -

 

[Don Guillett]

Look in the ThisWorkbook module for the before_save event

 

[Mike H]

Hi,

Alt+F11 to open VB editor. Double click 'This workbook' and paste this in on
the right . Change ranges and sheet to suit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not IsDate(Sheets("Sheet1").Range("A1")) _
Or IsEmpty(Sheets("Sheet1").Range("A2")) Then
MsgBox "Some sort of dire warning"
Cancel = True
End If
End Sub

Mike

 

[Mike H]

Hi,

I probaly meant before save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not IsDate(Sheets("Sheet1").Range("A1")) _
Or IsEmpty(Sheets("Sheet1").Range("A2")) Then
MsgBox "Some sort of dire warning"
Cancel = True
End If
End Sub


Mike

 

[cmac]

Got it - because I am not a programmer I need help in knowing the code to
check that cell T8 is not blank and the same for cell C3. If it is blank,
can we generate a dialog box informing the user that they are required fields?

Also, is there a way to automatically enable the macros when opening the
file without prompting the user now that we are adding a macro?

Thanks again -

 

[Gord Dibben]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Application.DisplayAlerts = False
With Sheets("Sheet1")
If .Range("C3") = "" Or .Range("T8") = "" Then
MsgBox "fill in C3 and T8"
Cancel = True
Else
ActiveWorkbook.Save
End If
End With
Application.DisplayAlerts = True
End Sub

This is workbook event code.

Right-click on the Excel Icon left of "File" and "View Code"

Copy/paste into that module.

As far as having no macros warning, without all users setting security to
"Low"(not recommended) you would need to digitally sign the workbook.


Gord Dibben MS Excel MVP

 

[cmac]

Thanks to all for your help - it works great and I am learning from each of
you.

 

[cmac]

Is there a way to save a template with these fields blank and then force the
check going forward?

 

[Gord Dibben]

Not sure what you mean.

By Template do you mean an *.xlt file that you create new workbooks from?

If so, you could add the code to the Template


Gord

 

[cmac]

Sorry,

Using an xls file that is a standard format that is distributed to others to
gather standard information in the same format. This file is made available
to the others who complete/provide the requested information.

 

[Gord Dibben]

Still not sure what "going forward" refers to.

The same workbook is distributed to others or copies of the workbook are
distributed to others?

Whatever the case, make sure the code is in every workbook that would be
sent out.


Gord

 

[cmac]

I have the initial copy of the file and would like for the fields that we do
not want left blank once distributed, to be blank on the initial
distribution. But because we are checking the fields prior to a save
function, I can't perform the initial save with these fields blank. Not sure
there is a way to handle this with the code, but I thought I would ask.

 

[Gord Dibben]

Disable events before the iniitial save with the two blank cells.

In VBE Immediate Window

application.enableevents = false

Will allow you to save the workbook with the cells blank then

application.enableevents = true


Gord