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