I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with
excell.

ANY help welcome!!!

I was given this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As BooleaÂ*n, _


Cancel As Boolean)


Dim cell As Range


For Each cell In Range("Required_Cells")


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.AddreÂ*ss


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

But I have no idea what it means. I put it in the right place (I think) and
I input the cells I want the users to fill in, butI keep getting this
message: "Compile error: user-defined type not defined".

I so have no knowledge of anything, quite clearly. I need it explained step
by step.

Cheers,

Rob


"JulieD" wrote:

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with
excell.

ANY help welcome!!!

Hi Rob

to use the code, right mouse click on a sheet tab and choose view / code -
in the VBE window you'll see down the left hand side your workbook's name in
bold & brackets ... under that will be things like sheet1,sheet2,
ThisWorkbook - the code needs to go into the "ThisWorkbook" section
- so double click on ThisWorkbook

then
on the top right side of the screen you have two drop down arrows - on the
one of the left choose Workbook
on the one on the right choose Before Save
then
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

will appear on your screen - the first line (starting with Private & ending
with Cancel As Boolean is all one line like in your screen)
now between these two lines you need to copy & paste the code

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 where it says "Sheet2" above change that to the sheet name you want to
check for entry on (leave the "") and where it says A1, A2, A3, A4 change
these to the actual cells you want to ensure have data in them.

once you've done that use ALT & F11 to switch back to your workbook and test
it.

Let us know how you go.

Cheers
JulieD


"abfabrob" wrote in message
...
I was given this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _


Cancel As Boolean)


Dim cell As Range


For Each cell In Range("Required_Cells")


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.Addre*ss


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

But I have no idea what it means. I put it in the right place (I think)
and
I input the cells I want the users to fill in, butI keep getting this
message: "Compile error: user-defined type not defined".

I so have no knowledge of anything, quite clearly. I need it explained
step
by step.

Cheers,

Rob


"JulieD" wrote:

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain
cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with
excell.

ANY help welcome!!!

It doesn't say "This Workbook" anywhere... the VBE screen is completely
blank...

Rob.

"JulieD" wrote:

Hi Rob

to use the code, right mouse click on a sheet tab and choose view / code -
in the VBE window you'll see down the left hand side your workbook's name in
bold & brackets ... under that will be things like sheet1,sheet2,
ThisWorkbook - the code needs to go into the "ThisWorkbook" section
- so double click on ThisWorkbook

then
on the top right side of the screen you have two drop down arrows - on the
one of the left choose Workbook
on the one on the right choose Before Save
then
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

will appear on your screen - the first line (starting with Private & ending
with Cancel As Boolean is all one line like in your screen)
now between these two lines you need to copy & paste the code

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 where it says "Sheet2" above change that to the sheet name you want to
check for entry on (leave the "") and where it says A1, A2, A3, A4 change
these to the actual cells you want to ensure have data in them.

once you've done that use ALT & F11 to switch back to your workbook and test
it.

Let us know how you go.

Cheers
JulieD


"abfabrob" wrote in message
...
I was given this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As BooleaÂ*n, _


Cancel As Boolean)


Dim cell As Range


For Each cell In Range("Required_Cells")


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.AddreÂ*ss


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

But I have no idea what it means. I put it in the right place (I think)
and
I input the cells I want the users to fill in, butI keep getting this
message: "Compile error: user-defined type not defined".

I so have no knowledge of anything, quite clearly. I need it explained
step
by step.

Cheers,

Rob


"JulieD" wrote:

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain
cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with
excell.

ANY help welcome!!!

Hi Rob

from the menu in the VBE screen choose view / project explorer

Cheers
JulieD

"abfabrob" wrote in message
...
It doesn't say "This Workbook" anywhere... the VBE screen is completely
blank...

Rob.

"JulieD" wrote:

Hi Rob

to use the code, right mouse click on a sheet tab and choose view /
code -
in the VBE window you'll see down the left hand side your workbook's name
in
bold & brackets ... under that will be things like sheet1,sheet2,
ThisWorkbook - the code needs to go into the "ThisWorkbook" section
- so double click on ThisWorkbook

then
on the top right side of the screen you have two drop down arrows - on
the
one of the left choose Workbook
on the one on the right choose Before Save
then
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

will appear on your screen - the first line (starting with Private &
ending
with Cancel As Boolean is all one line like in your screen)
now between these two lines you need to copy & paste the code

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 where it says "Sheet2" above change that to the sheet name you want
to
check for entry on (leave the "") and where it says A1, A2, A3, A4 change
these to the actual cells you want to ensure have data in them.

once you've done that use ALT & F11 to switch back to your workbook and
test
it.

Let us know how you go.

Cheers
JulieD


"abfabrob" wrote in message
...
I was given this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _


Cancel As Boolean)


Dim cell As Range


For Each cell In Range("Required_Cells")


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.Addre*ss


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

But I have no idea what it means. I put it in the right place (I think)
and
I input the cells I want the users to fill in, butI keep getting this
message: "Compile error: user-defined type not defined".

I so have no knowledge of anything, quite clearly. I need it explained
step
by step.

Cheers,

Rob


"JulieD" wrote:

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what
is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain
cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great
with
excell.

ANY help welcome!!!

I have a question about this same thing. I need to make inside sales fill out a spread sheet. I have the code that is listed in this forum. But I need to include each cell with a different message box. How do I tie it together. example C1 Message box - must enter todays date. C2 - must enter name and so on. This is what I have so far
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Cell As Range
For Each Cell In Sheets("Sheet1").Range("C3")
If IsEmpty(Cell.Value) Then
MsgBox "You must enter Today's Date" & Cell.Address
Application.Goto Cell
Cancel = True
Exit For
End If
Next Cell
End Sub
I have tried to enter code for each cell seperately but cannot get it to work. I am very new to this.

Julie, I tried the code you provided and it worked perfectly. However my
problem is slightly different. I would like users to enter an X in one of
cells E6, E7, E8, E9 or E10. Any one only. How can I change the code below to
accept an X in one cell only?

Thanks.
Ben Joseph

"JulieD" wrote:

Hi Rob

to use the code, right mouse click on a sheet tab and choose view / code -
in the VBE window you'll see down the left hand side your workbook's name in
bold & brackets ... under that will be things like sheet1,sheet2,
ThisWorkbook - the code needs to go into the "ThisWorkbook" section
- so double click on ThisWorkbook

then
on the top right side of the screen you have two drop down arrows - on the
one of the left choose Workbook
on the one on the right choose Before Save
then
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

will appear on your screen - the first line (starting with Private & ending
with Cancel As Boolean is all one line like in your screen)
now between these two lines you need to copy & paste the code

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 where it says "Sheet2" above change that to the sheet name you want to
check for entry on (leave the "") and where it says A1, A2, A3, A4 change
these to the actual cells you want to ensure have data in them.

once you've done that use ALT & F11 to switch back to your workbook and test
it.

Let us know how you go.

Cheers
JulieD


"abfabrob" wrote in message
...
I was given this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As BooleaÂ*n, _


Cancel As Boolean)


Dim cell As Range


For Each cell In Range("Required_Cells")


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.AddreÂ*ss


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

But I have no idea what it means. I put it in the right place (I think)
and
I input the cells I want the users to fill in, butI keep getting this
message: "Compile error: user-defined type not defined".

I so have no knowledge of anything, quite clearly. I need it explained
step
by step.

Cheers,

Rob


"JulieD" wrote:

Hi

following on from your discussion with Jason Morin (please stay in the
original thread) - what is the code you're getting the errors on, what is
the sheet name and cells that you want to make mandatory?

Cheers
JulieD

"abfabrob" wrote in message
...
I am creating an application form where I want users to fill certain
cells
in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with
excell.

ANY help welcome!!!

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In 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


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

You'll want to provide the worksheet name to check, too.

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

For Each cell In Me.Worksheets("Sheet1").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 wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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

Dim cell As Range

For Each cell In 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

"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

--

Dave Peterson

Dave Thanks.. this is what i put. i saved it and closed it out , when back in
and it isnt forcing doing anything... I am sorry for the confusion, I did
this years ago. But none of it has come back to me at all


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

For Each cell In
Expense_Report__BLANK.xls("MASTER").Range("d5,g5,j 5,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


"wkmooreh" wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In 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


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

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

Dave Thanks.. this is what i put. i saved it and closed it out , when back in
and it isnt forcing doing anything... I am sorry for the confusion, I did
this years ago. But none of it has come back to me at all

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

For Each cell In
Expense_Report__BLANK.xls("MASTER").Range("d5,g5,j 5,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

"wkmooreh" wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In 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


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

--

Dave Peterson

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


"wkmooreh" wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In 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


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

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.



wkmooreh wrote:

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

"wkmooreh" wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In 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


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

--

Dave Peterson