Validate cells have data

[Guest]

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 Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

Bob,

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

WendyUK

 

[Guest]

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
(e-mail address removed) and my business
(e-mail address removed)

thanks, Jim