prevent blank cells in excel
 

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

prevent blank cells in excel
 

I'd use adjacent columns near the cells that require input and use a formula
like:

=if(a1<"","","<--Please put something in this cell")

Format it in big, bold, red letters.



jojik wrote:

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

--

Dave Peterson

prevent blank cells in excel
 

hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea??
thanks for the response Dave.

"Dave Peterson" wrote:

I'd use adjacent columns near the cells that require input and use a formula
like:

=if(a1<"","","<--Please put something in this cell")

Format it in big, bold, red letters.



jojik wrote:

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

--

Dave Peterson

prevent blank cells in excel
 

hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea??
thanks for the response Dave.

"Dave Peterson" wrote:

I'd use adjacent columns near the cells that require input and use a formula
like:

=if(a1<"","","<--Please put something in this cell")

Format it in big, bold, red letters.



jojik wrote:

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

--

Dave Peterson

prevent blank cells in excel
 

First, is there a problem if it doesn't look good if the workbook isn't saved?
It seems that if this is irritating to the user, it should make it an even
better technique to make sure cells are filled in!

But ...

You could use an event macro that checks to see if the cells have something in
them--if they don't then the save is canceled.

But this kind of technique is easily bypassed by disabling macros or just
disabling events.

But if you want to try, this goes in the ThisWorkbook module (not a general
module, not in a worksheet module):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myRanges As Variant
Dim iCtr As Long

myRanges = Array(Me.Worksheets("Sheet1").Range("a1:a3,b7,c9") , _
Me.Worksheets("Sheet2").Range("c1:c2"), _
Me.Worksheets("Sheet3").Range("x1"))

For iCtr = LBound(myRanges) To UBound(myRanges)
With myRanges(iCtr)
If .Cells.Count < Application.CountA(.Cells) Then
MsgBox "Please fill in all the cells in: " _
& .Parent.Name & vbLf & .Address(0, 0)
Cancel = True
Exit For
End If
End With
Next iCtr
End Sub

You'll have to change the sheet names (probably not Sheet1, sheet2, sheet3) and
you'll have to change the cell addresses for each of those sheets.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

And when you (as a developer, not a user) want to save the workbook with those
cells empty, you'll have to disable events, save the workbook, and reenable
events.

One way is to finish your edits.
Open the VBE (use alt-f11)
Show the immediate window (use ctrl-g)
type this and hit enter:
application.enableevents = false

back to excel and save the workbook
back to the VBE's immediate window and type this and hit enter:
application.enableevents = false

And this is the same thing anyone can use to save the workbook with empty
cells.


jojik wrote:

hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea??
thanks for the response Dave.

"Dave Peterson" wrote:

I'd use adjacent columns near the cells that require input and use a formula
like:

=if(a1<"","","<--Please put something in this cell")

Format it in big, bold, red letters.



jojik wrote:

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

--

Dave Peterson


--

Dave Peterson

prevent blank cells in excel
 

sorry dave i was not aware about the macros and VB, and passed your code to a
friend and he did it for me. and now it is working as i wished. thanks a
lot...

"Dave Peterson" wrote:

First, is there a problem if it doesn't look good if the workbook isn't saved?
It seems that if this is irritating to the user, it should make it an even
better technique to make sure cells are filled in!

But ...

You could use an event macro that checks to see if the cells have something in
them--if they don't then the save is canceled.

But this kind of technique is easily bypassed by disabling macros or just
disabling events.

But if you want to try, this goes in the ThisWorkbook module (not a general
module, not in a worksheet module):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myRanges As Variant
Dim iCtr As Long

myRanges = Array(Me.Worksheets("Sheet1").Range("a1:a3,b7,c9") , _
Me.Worksheets("Sheet2").Range("c1:c2"), _
Me.Worksheets("Sheet3").Range("x1"))

For iCtr = LBound(myRanges) To UBound(myRanges)
With myRanges(iCtr)
If .Cells.Count < Application.CountA(.Cells) Then
MsgBox "Please fill in all the cells in: " _
& .Parent.Name & vbLf & .Address(0, 0)
Cancel = True
Exit For
End If
End With
Next iCtr
End Sub

You'll have to change the sheet names (probably not Sheet1, sheet2, sheet3) and
you'll have to change the cell addresses for each of those sheets.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

And when you (as a developer, not a user) want to save the workbook with those
cells empty, you'll have to disable events, save the workbook, and reenable
events.

One way is to finish your edits.
Open the VBE (use alt-f11)
Show the immediate window (use ctrl-g)
type this and hit enter:
application.enableevents = false

back to excel and save the workbook
back to the VBE's immediate window and type this and hit enter:
application.enableevents = false

And this is the same thing anyone can use to save the workbook with empty
cells.


jojik wrote:

hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea??
thanks for the response Dave.

"Dave Peterson" wrote:

I'd use adjacent columns near the cells that require input and use a formula
like:

=if(a1<"","","<--Please put something in this cell")

Format it in big, bold, red letters.



jojik wrote:

Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those
particular cells. can anybody tell me how to do this? thanks in advance...

--

Dave Peterson


--

Dave Peterson