Macro Validation

[scheduler]

I currently have a survey that is used by people nationally to rate a
department. They need to answer 9 questions then click "Submit" which has a
macro attached to save and hide their worksheet.

How can I add into the macro, code to stop the macro from running and
display an error box if someone tries to submit without having populated all
the fields (eg: cell L8)?

 

[Don Guillett]

As ALWAYS, post YOUR code for comments.

 

[scheduler]

The existing code is pretty simple, it just copies the responses in cells
L*"L28 into a hidden sheet (for result compilation), hides the sheet that
person was using and saves and closes the workbook. It is:

Private Sub CommandButton1_Click()

ActiveSheet.Select
Range("L8:L28").Select
Selection.Copy

Sheets("Ratings Results").Visible = True
Sheets("Ratings Results").Select
Sheets("Ratings Results").Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Sheets("Ratings Results").Range("A27:C27").Select
Sheets("Ratings Results").Visible = False

ActiveSheet.Select
Range("E4:J4").Select
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.Visible = False

MsgBox "Thank you for completing the Customer Service Satisfaction
Survey", vbOKOnly

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

 

[Don Guillett]

This will check to make sure 21 cells are not blank or "space barred". Then,
it will do the original. It is NOT necessary to unhide the destination sheet
or select it.

Sub doitright()
'check range
If Evaluate("SumProduct(--(Len(trim(L8:L28)) > 0))") < 21 Then
MsgBox "Please complete All items"
Exit Sub
End If
'do the copy insert
Range("L8:L28").Copy
Sheets("Ratings Results").Columns("F").Insert
Application.CutCopyMode = False
MsgBox "Thank you for completing the Customer" & _
"Service Satisfaction Survey", vbOKOnly

End Sub

 

[scheduler]

Thanks Don,

It worked a charm (and made the whole code a lot smaller).

Is there a way to get the macro to also check that if those cells (lets say
K8) is populated but with say a 1, 2 or 3 rating, that another cell (say M8)
must be also be populated (with a comment)?

 

[Don Guillett]

I can't tell wo seeing your file. If you like, send to my address below
along with these snippets on an inserted sheet along with clear explanations
and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Don,

It worked a charm (and made the whole code a lot smaller).

Is there a way to get the macro to also check that if those cells (lets
say
K8) is populated but with say a 1, 2 or 3 rating, that another cell (say
M8)
must be also be populated (with a comment)?