Solved: Force user to fill cell

[sujittalukde]

In the attached file if the user does not filled in some data, a message box should appear before closing the wb, and force the user to fill those data before closing the workbook so that even by mistake user does not forget to fill those cells.

[Bob Phillips]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet1")
If .Range("D2").Value = "" Then
MsgBox "Month must be supplied"
Cancel = True
ElseIf .Range("E2").Value = "" Then
MsgBox "Year must be supplied"
Cancel = True
End If
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[sujittalukde]

But it does not cover the cells related to number entries ie green coloured cells!

[lucas]

Bob showed you how to do it and got you started. You have to do the busy work yourself...I did the next one for you cell B5:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("Sheet1")
If .Range("D2").Value = "" Then
MsgBox "Month must be supplied"
Cancel = True
ElseIf .Range("E2").Value = "" Then
MsgBox "Year must be supplied"
Cancel = True
ElseIf .Range("B5").Value = "" Then
MsgBox "Cell B5 Must be filled in"
Cancel = True
Range("B5").Select
End If
End With
End Sub

[sujittalukde]

Thanks lucas, rest I can do One thing , Can I set a range in macro in place of cell B5,B6,etc. something like B5:E7,B9:E12 If yes, just supply the lines i will modify the same in the code

[Bob Phillips]

ElseIf Application.Counta(.Range("B5:E7,B9:E12")) = 0 Then

[sujittalukde]

thanks xld!

[geekgirlau]

Actually, in order to make sure all of those cells are filled in I think you'll need:


ElseIf Application.Counta(.Range("B5:E7,B9:E12")) <> 28 Then

[sujittalukde]

Thanks geek !

[Bob Phillips]

or to not be range size bound



ElseIf Application.CountBlank(.Range("B5:E7,B9:E12")) = 0 Then

[sujittalukde]

Again, Thanks to you, xld !

[Bob Phillips]

Okay, sorry I got it wrong first time.