PDA

View Full Version : Solved: Prevent Users from leaving blank cells



Poundland
09-17-2008, 04:23 AM
Dear all,

I copied this code from another post and have tried to adapt it to my situation but are not having much luck.

When I step through the code it shows a Compile Error on the Me.Range code, and generally the code does not work.

I would like to validate an entry into the column A cell, and if cells in the same row in Columns C & P are empty then to return the error message.

Also how can I incorporate into this code a function to prevent the user from saving if the validation has not been met.

Not sure if it makes a difference but the file will be shared, I tried the code without the sharing the workbook and it did not work.

Any help you could give me would be most appreciated.

Private Sub Workbook_Open()
Dim i, c, j As Integer
Dim msg As String
Dim cel As Range
c = 0: j = 0

For i = 377 To 10000
For Each cel In Me.Range("A" & i)



If cel.Value <> "" And cel.Offset(, 3) = "" Then
msg = msg & "In column ""C""" & vbTab & cel.Offset(, 3).Address & vbNewLine
c = c + 1
End If

If cel.Value <> "" And cel.Offset(, 15) = "" Then
msg = msg & "In column ""P""" & vbTab & cel.Offset(, 15).Address & vbNewLine
j = j + 1
End If



Next cel
Next i

If c = 0 And j = 0 Then
Exit Sub
Else
Me.Activate
MsgBox "Please fill in these cells: " & vbNewLine & msg
End If
End Sub

Bob Phillips
09-17-2008, 04:59 AM
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Long
Dim msg As String

With Worksheets("Sheet1")

For i = 37 To 10000

If .Cells(i, "A").Value <> "" Then

If .Cells(i, "C").Value = "" Then
msg = msg & "Cell " & vbTab & .Cells(i, "C").Address & vbNewLine
End If

If .Cells(i, "P").Value = "" Then
msg = msg & "Cell " & vbTab & .Cells(i, "P").Address & vbNewLine
End If
End If
Next i

If msg = "" Then
Exit Sub
Else
.Activate
MsgBox "Please fill in these cells and then save: " & vbNewLine & vbNewLine & msg
Cancel = True
End If
End With

End Sub

Poundland
09-17-2008, 09:52 AM
Xld,

Thanks this works a charm.

david000
09-18-2008, 01:45 AM
If msg = "" Then Exit Sub
Nice!:clap: