No Blank Cell

[George]

Hi.
I am looking for a macro to not let blank and select the cell before
printing..
Thank you.

 

[Dave Peterson]

I think I'd use an adjacent cell with a warning in it that clutters the
printout:

=if(a1="","","Invalid Report. A1 is not filled in!!!")
and give it a nice bold, red color.

But if you want, you could use an event macro. This goes behind the workbook
module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Me.Worksheets("sheet1").Range("a1")
If IsEmpty(.Value) Then
Application.Goto .Cells, scroll:=True
MsgBox "Can't print without that cell being filled"
Cancel = True
End If
End With
End Sub

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

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/events.htm

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

And the macro will not work if the user disables macros or disables events.

 

[George]

It works fine but I wanted to select the cell that left blank and add more
than 1 celle then
in VBA it types
Compile Error
Ambiguous name detected shovs up
is there a way to add more than 1 cell

Sincere Thanks...........
George.

 

[Dave Peterson]

When you change the code, it's a good idea to post what you tried. It may give
a hint to what you need.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Worksheets("sheet1").Range("a1,b3,c12:c18")

If Application.CountA(myRng) = myRng.Cells.Count Then
'all filled in, do nothing
Else
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Value) Then
Application.Goto .Cells, scroll:=True
MsgBox "Can't print without all cells in: " _
& myRng.Address(0, 0) & " filled"
Cancel = True
Exit For
End If
End With
Next myCell
End If
End Sub

 

[SkippyPB]

When you change the code, it's a good idea to post what you tried. It may give
a hint to what you need.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Worksheets("sheet1").Range("a1,b3,c12:c18")

If Application.CountA(myRng) = myRng.Cells.Count Then
'all filled in, do nothing
Else
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Value) Then
Application.Goto .Cells, scroll:=True
MsgBox "Can't print without all cells in: " _
& myRng.Address(0, 0) & " filled"
Cancel = True
Exit For
End If
End With
Next myCell
End If
End Sub

Dave this looks like something I need as well but with a slightly
different requirement.

The spreadsheet is generally never printed (too big!) but is always
saved, so could i use your suggestion with BeforeSave instead of
BeforePrint?

Secondly I know which columns in the spreadsheet should not be blank
but the rows would vary depending on how much data was entered? What
would I need to change in your suggestion to accomplish that?

Finally, when a blank cell is found I'd like to put the cursor/pointer
on that cell with a message box.

Thanks for any suggestions you might have.

////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Dave Peterson]

You could use it in the beforesave event. But remember, it might make it more
difficult for you to save the file when you're developing it. You'd want to
give yourself a way to save without filling in those cells--maybe checking
application.username to see if it was you.

If your data has a column that could be used to determine that last used row,
then you could check all the cells between row 2 (say) and that last used row in
certain columns.

Personally, as a user, I would find this irritating. If I wanted to save and go
to lunch and come back, your code would stop me. Even if I made significant
changes that I was afraid to lose, your code may stop me.

And these lines:
Selected the first cell that was empty and gave a message box.

 

[SkippyPB]

You could use it in the beforesave event. But remember, it might make it more
difficult for you to save the file when you're developing it. You'd want to
give yourself a way to save without filling in those cells--maybe checking
application.username to see if it was you.

If your data has a column that could be used to determine that last used row,
then you could check all the cells between row 2 (say) and that last used row in
certain columns.

Personally, as a user, I would find this irritating. If I wanted to save and go
to lunch and come back, your code would stop me. Even if I made significant
changes that I was afraid to lose, your code may stop me.

I agree with that but I couldn't figure out a good way to do it while
the user was entering data. There was a suggestion about looking at a
previous cell when something was entered, but it used hard coded cell
addresses and that would not work well with this spreadsheet.

Thanks for your suggestions.

And these lines:

Selected the first cell that was empty and gave a message box.

////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve