Required Data in cell
 

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 -

Required Data in cell
 

Look in the ThisWorkbook module for the before_save event

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Required Data in cell
 

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

"cmac" wrote:

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 -

Required Data in cell
 

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

"Mike H" wrote:

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

"cmac" wrote:

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 -

Required Data in cell
 

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 -

"Don Guillett" wrote:

Look in the ThisWorkbook module for the before_save event

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Required Data in cell
 

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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

"Mike H" wrote:

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

"cmac" wrote:

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 -

Required Data in cell
 

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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

On Thu, 16 Oct 2008 13:31:02 -0700, cmac
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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" wrote:

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

On Thu, 16 Oct 2008 13:31:02 -0700, cmac
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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

On Fri, 17 Oct 2008 05:14:10 -0700, cmac
wrote:

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" wrote:

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

On Thu, 16 Oct 2008 13:31:02 -0700, cmac
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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" wrote:

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

On Fri, 17 Oct 2008 05:14:10 -0700, cmac
wrote:

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" wrote:

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

On Thu, 16 Oct 2008 13:31:02 -0700, cmac
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -

Required Data in cell
 

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

On Fri, 17 Oct 2008 11:14:06 -0700, cmac
wrote:

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" wrote:

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

On Fri, 17 Oct 2008 05:14:10 -0700, cmac
wrote:

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" wrote:

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

On Thu, 16 Oct 2008 13:31:02 -0700, cmac
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 8 Oct 2008 12:22:04 -0700, cmac
wrote:

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 -