How to force data to be put in entry

  • Thread starter Thread starter Jac Schroeten
  • Start date Start date
J

Jac Schroeten

When anther row (line) is added to a worksheet I want to
be made sure within the row a certain column is filled in
before saving is done.

How do I create that?

Thx,

Jac
 
Hi Jac

You can use this event in the Thisworkbook module to
check if all cells in the C column in Sheet1 have a value
If not you can't save the file

See also this event

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
RC = Sheets("Sheet1").UsedRange.Rows.Count
If Application.WorksheetFunction.CountA _
(Sheets("Sheet1").UsedRange.Columns(3)) <> RC Then
MsgBox "fill in all cells in column C"
Cancel = True
End If
End Sub

Right click on the Excel icon next to File in the menubar
And choose View code

You are now in the Thisworkbook module
Paste the Event in this place
Alt-Q to go back to Excel
Save your file(if you can,g>)
 
I named a bunch of cells on Sheet1: MustBeFilled

Then I put this code behind the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("mustbefilled")

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Noooooo! You must complete the form before you save!"
Cancel = True
End If

End Sub

But this only gets caught when the user tries to save. (I'd find it
irritating--I may want to save before I'm done entering data.)

And this fails if the user disables macros, too (or just disables events).

And since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Ron,

Thank you very very much, this macro was exactly what I
was looking for.

Jac

ps:
Dave,
Thank you for replying too, I added the site you mentioned
to my list :o)

-----Original Message-----
Hi Jac

You can use this event in the Thisworkbook module to
check if all cells in the C column in Sheet1 have a value
If not you can't save the file

See also this event

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
RC = Sheets("Sheet1").UsedRange.Rows.Count
If Application.WorksheetFunction.CountA _
(Sheets("Sheet1").UsedRange.Columns(3)) <> RC Then
MsgBox "fill in all cells in column C"
Cancel = True
End If
End Sub

Right click on the Excel icon next to File in the menubar
And choose View code

You are now in the Thisworkbook module
Paste the Event in this place
Alt-Q to go back to Excel
Save your file(if you can,g>)


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Jac Schroeten" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
Back
Top