Making cells mandatory to fill in

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!!!

It totally worked, thank you!!!!!!!!!!!!!!!!

"JulieD" wrote:

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!!!

you're welcome ... now make sure you've set security to medium (and choose
enable macros when you open the workbook) or it won't work if you close the
book (tools / macro / security tab)

Cheers
JulieD

"abfabrob" wrote in message
...
It totally worked, thank you!!!!!!!!!!!!!!!!

"JulieD" wrote:

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!!!

if i want when A1 is entered, B1 must be entered also.
then when A2 has to be entered, B2 has to be entered too.
then...
if the forumla is apply to B3,B4&b5
but i only need uers to fill in A3 first, A4&A5 will be filled next time.
then it will keep promp users to enter B4&B5 which I dont want.
any helps?
thanks!

"JulieD" wrote:

you're welcome ... now make sure you've set security to medium (and choose
enable macros when you open the workbook) or it won't work if you close the
book (tools / macro / security tab)

Cheers
JulieD

"abfabrob" wrote in message
...
It totally worked, thank you!!!!!!!!!!!!!!!!

"JulieD" wrote:

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!!!

any tips?

"kyoshirou" wrote:

if i want when A1 is entered, B1 must be entered also.
then when A2 has to be entered, B2 has to be entered too.
then...
if the forumla is apply to B3,B4&b5
but i only need uers to fill in A3 first, A4&A5 will be filled next time.
then it will keep promp users to enter B4&B5 which I dont want.
any helps?
thanks!

"JulieD" wrote:

you're welcome ... now make sure you've set security to medium (and choose
enable macros when you open the workbook) or it won't work if you close the
book (tools / macro / security tab)

Cheers
JulieD

"abfabrob" wrote in message
...
It totally worked, thank you!!!!!!!!!!!!!!!!

"JulieD" wrote:

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!!!