Validate cells have data
 

Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Wendy,

Try something like

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

With Worksheets("Sheet1")
If Range("A1").Value < "" And _
Range("A2").Value < "" And _
Range("A3").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

This goes in the Thisworkbook code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Hi Bob,

Thank you for your speedy answer- I have put the code into "This workbook"
and listed the cells I wish to validate. I have received no error, but it
will still save if one of these fields is blank. Any suggestions?

Wendy

"Bob Phillips" wrote:

Wendy,

Try something like

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

With Worksheets("Sheet1")
If Range("A1").Value < "" And _
Range("A2").Value < "" And _
Range("A3").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

This goes in the Thisworkbook code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Hi Wendy,

Can you show your amended code and highlight where the error occurs?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi Bob,

Thank you for your speedy answer- I have put the code into "This
workbook"
and listed the cells I wish to validate. I have received no error, but it
will still save if one of these fields is blank. Any suggestions?

Wendy

"Bob Phillips" wrote:

Wendy,

Try something like

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

With Worksheets("Sheet1")
If Range("A1").Value < "" And _
Range("A2").Value < "" And _
Range("A3").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

This goes in the Thisworkbook code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi

I wish to validate that certain cells have been populated with data
and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

This is a cut & paste of the code - I do not get an error, it just saves the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Sorry Wendy,

Seems I forgot my dots. Try this instead

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

With Worksheets("Job Card")
If .Range("F1").Value < "" And _
.Range("J1").Value < "" And _
.Range("I4").Value < "" And _
.Range("I6").Value < "" And _
.Range("B2").Value < "" And _
.Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
This is a cut & paste of the code - I do not get an error, it just saves
the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Hi Bob,

I Cut & Pasted your code straight in - it doesn't complain at all - no errors.

Opened the sheet again but still saves with blank fields. It is bound to be
me and something stupid I am doing........ but I do thank you for your
wonderful efforts and patience with me.....

WendyUK

"Bob Phillips" wrote:

Sorry Wendy,

Seems I forgot my dots. Try this instead

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

With Worksheets("Job Card")
If .Range("F1").Value < "" And _
.Range("J1").Value < "" And _
.Range("I4").Value < "" And _
.Range("I6").Value < "" And _
.Range("B2").Value < "" And _
.Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
This is a cut & paste of the code - I do not get an error, it just saves
the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Wendy,

It worked in my tests (doesn't it always).

Why don't you send me the workbook and I can see it first hand.

bob dot phillips at tiscali dot co dot uk

do the obvious to the above email

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi Bob,

I Cut & Pasted your code straight in - it doesn't complain at all - no
errors.

Opened the sheet again but still saves with blank fields. It is bound to
be
me and something stupid I am doing........ but I do thank you for your
wonderful efforts and patience with me.....

WendyUK

"Bob Phillips" wrote:

Sorry Wendy,

Seems I forgot my dots. Try this instead

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

With Worksheets("Job Card")
If .Range("F1").Value < "" And _
.Range("J1").Value < "" And _
.Range("I4").Value < "" And _
.Range("I6").Value < "" And _
.Range("B2").Value < "" And _
.Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
This is a cut & paste of the code - I do not get an error, it just
saves
the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data
and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Bob,

I can't thank you enough.............. terrific, you have saved me a lot of
work!!!!

WendyUK

"Bob Phillips" wrote:

Wendy,

It worked in my tests (doesn't it always).

Why don't you send me the workbook and I can see it first hand.

bob dot phillips at tiscali dot co dot uk

do the obvious to the above email

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi Bob,

I Cut & Pasted your code straight in - it doesn't complain at all - no
errors.

Opened the sheet again but still saves with blank fields. It is bound to
be
me and something stupid I am doing........ but I do thank you for your
wonderful efforts and patience with me.....

WendyUK

"Bob Phillips" wrote:

Sorry Wendy,

Seems I forgot my dots. Try this instead

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

With Worksheets("Job Card")
If .Range("F1").Value < "" And _
.Range("J1").Value < "" And _
.Range("I4").Value < "" And _
.Range("I6").Value < "" And _
.Range("B2").Value < "" And _
.Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
This is a cut & paste of the code - I do not get an error, it just
saves
the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data
and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy

Hi Bob,
Unfortunately I am not as tech-savvy as Wendy, however I think I am trying
to accomplish the same goal as her. I have a simple Excel worksheet that i
need my salesmen to fill out all fields completely before it is submitted. i
thought data validation held the answer, but to no avail. Is it possible for
you to explain (in layman terms) how i can implement the instructions you
gave wendy. if necessary, i can send you a copy of the worksheet on Saturday.

i am new to discussion groups so i don't know how long it takes to get a
response. If you don't mind, reply to both my son's home email
and my business


thanks, Jim

"Bob Phillips" wrote:

Wendy,

It worked in my tests (doesn't it always).

Why don't you send me the workbook and I can see it first hand.

bob dot phillips at tiscali dot co dot uk

do the obvious to the above email

--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
Hi Bob,

I Cut & Pasted your code straight in - it doesn't complain at all - no
errors.

Opened the sheet again but still saves with blank fields. It is bound to
be
me and something stupid I am doing........ but I do thank you for your
wonderful efforts and patience with me.....

WendyUK

"Bob Phillips" wrote:

Sorry Wendy,

Seems I forgot my dots. Try this instead

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

With Worksheets("Job Card")
If .Range("F1").Value < "" And _
.Range("J1").Value < "" And _
.Range("I4").Value < "" And _
.Range("I6").Value < "" And _
.Range("B2").Value < "" And _
.Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"WendyUK" wrote in message
...
This is a cut & paste of the code - I do not get an error, it just
saves
the
document.

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

With Worksheets("Job Card")
If Range("F1").Value < "" And _
Range("J1").Value < "" And _
Range("I4").Value < "" And _
Range("I6").Value < "" And _
Range("B2").Value < "" And _
Range("B7").Value < "" Then
Else
MsgBox "Not all fields completed"
Cancel = True
End If
End With

End Sub

Regards
WendyUK



"WendyUK" wrote:

Hi

I wish to validate that certain cells have been populated with data
and
prevent the "save" function if these cells are "blank".

Can anyone help?

Thank you,
Wendy