Making cells mandatory to fill in

[Dave Peterson]

Try this:

For Each cell In me.worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10")

Me refers to the object that owns the code. In this case, since the code is in
the ThisWorkbook module, the Me keyword refers to the workbook that's closing.

And in this case, it doesn't matter, but I like this syntax:

For Each cell In me.worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10").cells

I think it makes it more self-documenting.

 

[wkmooreh]

Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

 

[wkmooreh]

Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

 

[Sri]

Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

--
w
















- Show quoted text -

Check the below article, hope this helps.

http://funwithexcel.blogspot.com/2009/04/validating-form-input-and-forcing-user.html

 

[Sri]

Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

--
w
















- Show quoted text -

Check the below article, hope this helps.

http://funwithexcel.blogspot.com/2009/04/validating-form-input-and-forcing-user.html

 

[Dave Peterson]

So you want to give you (as a developer) special dispensation and allow yourself
to save and close the workbook with those cells empty.

You have a few choices.

I'd do this:
Open the VBE
Hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false
Then back to excel and save/close the workbook.
Then back to the immediate window in the VBE:
application.enableevents = true

The workbook_BeforeSave procedure is one of those events that excel keeps
looking for. By telling it to stop looking at all events, you can do what you
want.

Notice that this isn't difficult to do. Any one who knows a little excel can do
exactly the same thing. So your _BeforeSave event isn't really secure.

=========
If you are only using this workbook as a developer and never want to have this
code stop you from saving, you can add a couple of lines to check to see who's
using the workbook:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

If Application.UserName = "Your Name Here" Then
Exit Sub
End If

For Each cell In Me.Worksheets("Sheet1").Range("d5,g5,j5,e7,m7,g10").Cells
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

To make sure you spell your username correctly, type this into the immediate
window:
?application.username

And use that name in your code.

 

[Dave Peterson]

So you want to give you (as a developer) special dispensation and allow yourself
to save and close the workbook with those cells empty.

You have a few choices.

I'd do this:
Open the VBE
Hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false
Then back to excel and save/close the workbook.
Then back to the immediate window in the VBE:
application.enableevents = true

The workbook_BeforeSave procedure is one of those events that excel keeps
looking for. By telling it to stop looking at all events, you can do what you
want.

Notice that this isn't difficult to do. Any one who knows a little excel can do
exactly the same thing. So your _BeforeSave event isn't really secure.

=========
If you are only using this workbook as a developer and never want to have this
code stop you from saving, you can add a couple of lines to check to see who's
using the workbook:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

If Application.UserName = "Your Name Here" Then
Exit Sub
End If

For Each cell In Me.Worksheets("Sheet1").Range("d5,g5,j5,e7,m7,g10").Cells
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

To make sure you spell your username correctly, type this into the immediate
window:
?application.username

And use that name in your code.

 

[Pam M]

Gord--I followed these instructions to save the workbook template but still
get the message that the cells need to be filled out. I cannot even click
Save-As--the message pops up immediately. Any thoughts?

 

[Dave Peterson]

Try the instructions about disabling events once more.

 

[Pam M]

I did--still no luck. Here is what I am doing.

Clicking View - Immediate Window
Entering Application.EnableEvents = False into the immediate window
Back to Excel, clicking File-Save As, but can't because I get the pop up to
force entry into the cell.

 

[Dave Peterson]

After you type that "application.enableevents = false" into the immediate
window, are you hitting enter?

This command tells excel to stop looking for events completely. So it should
work for you. (I've never seen it fail.)

If the next test fails, enter this command right after the first:

?application.enableevents

That should return True or False.

If you see true, then events are enabled and your SaveAs will fire that event.

If you see false (I hope!), then events are disabled and you should be able to
do the SaveAs.

 

[Pam M]

Dave; Never underestimate the power of hitting the enter key. lol--duh!
Thanks so much. : ) Pam

 

[Pam M]

One more quick question Dave--can I set the application.enable events = false
or true within a macro so that it disables events in order to save the
template, but then re-enables them?

 

[Dave Peterson]

Sure.

Make a macro that only you know the name of and make it invisible to the alt-f8
dialog.

In a separate module:

Option Explicit
Option Private Module
Sub PamsSaveAs()
application.enableevents = false
thisworkbook.save 'save the current file??
application.enableevents = true
End Sub

One more quick question Dave--can I set the application.enable events = false
or true within a macro so that it disables events in order to save the
template, but then re-enables them?

 

[Pam M]

Perfect! Thanks so much Dave--you're the best.

Sure.

Make a macro that only you know the name of and make it invisible to the alt-f8
dialog.

In a separate module:

Option Explicit
Option Private Module
Sub PamsSaveAs()
application.enableevents = false
thisworkbook.save 'save the current file??
application.enableevents = true
End Sub