No Blank Cell
 

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

No Blank Cell
 

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 wrote:

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

--

Dave Peterson

No Blank Cell
 

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

No Blank Cell
 

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



George wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson