I have the following code in a cell:
rivate Sub Workbook_beforeclose(Cancel As Boolean)
Dim j As Integer


If Trim(Me.Worksheets("competency").Range("C3").Value ) = "" Then
MsgBox "Cell C3 is empty. Please fill it"
Cancel = True
Exit Sub
End If


For j = 1 To Len(Me.Worksheets("competency").Range("C3"))
If ((Asc(UCase(Mid(Me.Worksheets("competency").Range( "C3").Value
j, 1))) Asc("Z")) Or _

(Asc(UCase(Mid(Me.Worksheets("competency").Range(" C3").Value, j, 1)))
Asc("A"))) _
And (Mid(Me.Worksheets("competency").Range("C3").Value
j, 1) < " ") Then
MsgBox "Cell C3 contains illegal characters, only letters"
Cancel = True
Exit Sub
End If
Next j

If I want to apply this code to mulptiple cells where should I put th
other code? Should it be part of J loop, or should I make another one
I've tried it either way and it has not worked, so if anyone can b
specific and help me I would GREATLY appreciate it

--
Message posted from http://www.ExcelForum.com

Private Sub Workbook_beforeclose(Cancel As Boolean)
Dim sh as worksheet, cell as Range
Dim s1 as String
set sh = me.worksheets("competency")
for each cell in sh.Range("C3:C10,D9,F15")
if isempy(cell) then
msgbox Cell.Address & " is empty, please fill it"
cancel = true
end if
s1 = cell.Value
if s1 Like "*[0-9]*" then
MsgBox "Cell " & cell.address & " contains illegal characters, only
letters"
cancel = True
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"jmorgs " wrote in message
...
I have the following code in a cell:
rivate Sub Workbook_beforeclose(Cancel As Boolean)
Dim j As Integer


If Trim(Me.Worksheets("competency").Range("C3").Value ) = "" Then
MsgBox "Cell C3 is empty. Please fill it"
Cancel = True
Exit Sub
End If


For j = 1 To Len(Me.Worksheets("competency").Range("C3"))
If ((Asc(UCase(Mid(Me.Worksheets("competency").Range( "C3").Value,
j, 1))) Asc("Z")) Or _

(Asc(UCase(Mid(Me.Worksheets("competency").Range(" C3").Value, j, 1))) <
Asc("A"))) _
And (Mid(Me.Worksheets("competency").Range("C3").Value ,
j, 1) < " ") Then
MsgBox "Cell C3 contains illegal characters, only letters"
Cancel = True
Exit Sub
End If
Next j

If I want to apply this code to mulptiple cells where should I put the
other code? Should it be part of J loop, or should I make another one?
I've tried it either way and it has not worked, so if anyone can be
specific and help me I would GREATLY appreciate it!


---
Message posted from http://www.ExcelForum.com/