Excel VBA - Complicated Problem

  • Thread starter Thread starter ajlove20
  • Start date Start date
A

ajlove20

Hi,

I was given a task to create a workbook with 5 worksheets. On eac
worksheet I am suppose to created fields for another person to fill in
For example, I will have:

Name Hrs Wage Payment

and then the other person will fill it in accordingly.

So in the end we will have something like this:

Name Hrs Wage Payment
John 5 2.50 12.50
Mark 20 1.00 20.00
Thomas 10 5.00 50.00


But let's say there is an item missing, such as 10.

Is there a code that can run when I attempt to close the file to searc
for empty fields on each worksheet and have a box pop up and displa
which field was missing before the file closes?

Thank you. Any input would be helpful. Thanks in advance.

a
 
Hi
though this could be done I would consider creating a Userform which
does the checking after each record.
 
Or this

Right click on the excel icon next to file on the menubar
choose view code

The ThisWorkbook is now active
Paste the code in there

Every time you try to close the workbook the Workbook_BeforeClose
run and check the cells for you.
If not all the cells have a value you can't save


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:D4")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "Fill in all cells"
Cancel = True
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


Frank Kabel said:
Hi
though this could be done I would consider creating a Userform which
does the checking after each record.
Click to expand...
 
If not all the cells have a value you can't save

Close I mean

--
Regards Ron de Bruin
http://www.rondebruin.nl


Ron de Bruin said:
Or this

Right click on the excel icon next to file on the menubar
choose view code

The ThisWorkbook is now active
Paste the code in there

Every time you try to close the workbook the Workbook_BeforeClose
run and check the cells for you.
If not all the cells have a value you can't save


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:D4")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "Fill in all cells"
Cancel = True
End If
End Sub
Click to expand...
 
Hi,

Thank you. Your ideas were wonderful. I was also wondering where
can possibly look more into Userforms.

a
 
Back
Top