I am trying to trap users from closing a spreadsheet if not all require
fields are entered. I have 4 tabs that contain required fields. Th
code below is a test to ensure that I can trap them. However, I pla
to have this code available on numerous spreadsheets but the amount o
rows for each spreadsheet may be different. Therefore, I am trying t
figure out how I can determine what the range should be and incorporat
into this code. Thanks in advance for your help

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.CountA(Worksheets("Project").Range("A2 :J2")) < 10 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Schedule").Range("A 2:D2")) < 4 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Budget").Range("A2: L2")) < 12 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Resource").Range("A 2:D2")) < 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End If
End If
End If
End Su

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cLastCol As Long
Dim rng As Range

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Worksheets("Project").Range("A2", Cells(2, cLastCol))
If Application.CountA(rng) < 10 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Worksheets("Schedule").Range("A2", Cells(2, cLastCol))
If Application.CountA(rng) < 4 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Worksheets("Budget").Range("A2", Cells(2, cLastCol))
If Application.CountA(rng) < 12 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Worksheets("Resource").Range("A2", Cells(2, cLastCol))
If Application.CountA(rng) < 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"donfon13 " wrote in message
...
I am trying to trap users from closing a spreadsheet if not all required
fields are entered. I have 4 tabs that contain required fields. The
code below is a test to ensure that I can trap them. However, I plan
to have this code available on numerous spreadsheets but the amount of
rows for each spreadsheet may be different. Therefore, I am trying to
figure out how I can determine what the range should be and incorporate
into this code. Thanks in advance for your help

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.CountA(Worksheets("Project").Range("A2 :J2")) < 10 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Schedule").Range("A 2:D2")) < 4 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Budget").Range("A2: L2")) < 12 Then
MsgBox "required fields missing"
Cancel = True
Else
If Application.CountA(Worksheets("Resource").Range("A 2:D2")) < 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End If
End If
End If
End Sub


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

I appreciate the assistance however I received the following error:

Run-time error '1004'
Application-defined or object defined error

the VB script editor highlighted this row

Set rng = Worksheets("Project").Range("A2", Cells(2, cLastCol))

Thoughts

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

This is an update on the code but I'm not sure I am properly understanding
what you want, as finding a dynamic last column doesn't add much

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cLastCol As Long
Dim rng As Range

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Project")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) < 10 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Schedule")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) < 4 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Budget")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) < 12 Then
MsgBox "required fields missing"
Cancel = True
Exit Sub
End If

cLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
With Worksheets("Resource")
Set rng = .Range("A2", Cells(2, cLastCol))
End With
If Application.CountA(rng) < 4 Then
MsgBox "required fields missing"
Cancel = True
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"donfon13 " wrote in message
...
I appreciate the assistance however I received the following error:

Run-time error '1004'
Application-defined or object defined error

the VB script editor highlighted this row

Set rng = Worksheets("Project").Range("A2", Cells(2, cLastCol))

Thoughts?


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