Cells with required entry

Posted by Phil on July 13, 2001 6:40 AM

Is there a way of making the entry of data into a cell required - i.e. you can't close or save the workbook unless that cell has a value in it? A bit like the require field property in Access, I suppose

Hope anyone can help

Cheers
Phil

Posted by Ivan F Moala on July 13, 2001 7:32 AM

Phil
Place code in the Thisworkbook object
1) Alt F11
2) Ctrl R
3) double click Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
End If
End Sub

Ivan

Posted by David Megnin on July 13, 2001 8:44 AM

Ivan (or anyone),

How would you do this for required checkboxes and option boxes? (Forms type)

Thanks,
David

Posted by David Megnin on July 13, 2001 9:49 AM

Question: Once you have this code in place how do you save your Worksheet? It needs to be saved with empty cells and code in place. Is this a catch-22?

Thanks.
David

Posted by Ivan F Moala on July 13, 2001 4:00 PM

Not at all...just put in a test or password string
and delete it upon saving via code...eg

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
If Sheets("yourSheet").[iv1] &LT;&GT; "save" Then
Cancel = True
MsgBox "Can't save No entry for A1_BC"
End If
Else
MsgBox "BC"
Sheets("yourSheet").[iv1] = ""
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
If Sheets("yourSheet").[iv1] &LT;&GT; "save" Then
Cancel = True
MsgBox "Can't save No entry for A1_BS"
End If
Else
Application.EnableEvents = False
MsgBox "BS"
Sheets("yourSheet").[iv1] = ""
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_Open()
Sheets("yourSheet").[iv1] = ""
End Sub

Posted by Ivan F Moala on July 14, 2001 4:25 AM

Something like this will get you started.....
Lookup QueryClose

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent user from closing if Checkbox is not checked.
If CloseMode = 0 Then
If CheckBox1.Value = False Then
Cancel = 1
MsgBox "Checkbox not Ticked!"
End If
End If

End Sub Ivan (or anyone), How would you do this for required checkboxes and option boxes? (Forms type) Thanks,

Posted by Phil on July 14, 2001 1:09 PM

It works!!

Thanks everyone for your time and effort

Phil