Excel/VBA Don’t print if cell is empty (Excel 97)

Topic

New Reply

If the first 3 conditionals below are false, I still want to print if cell B1299 has text in it. The first 3 conditionals come from checkboxes. cellB1299 is formatted as text.

When I try the Not IsNull(B1299), it is always printing whether there is text in the cell or if it is left blank.
I have also tried if B1299=

Reply | Quote

Replies

You want to use the Range method. Here’s how I would do it:

Option Explicit
Sub Macro3()
Dim ExtraPage As Boolean, ExtraComments As Boolean
Dim ExtraCollateral As Boolean
'   Next three lines replaced with your code
    ExtraPage = True
    ExtraComments = True
    ExtraCollateral = True
    With Sheets("Extra Notes")
        If ExtraPage Or ExtraComments Or ExtraCollateral _
        Or .Range("B1299")  "" Then _
            .PrintOut
    End With
End Sub

Notice that there is a period before Range, so that Excel knows the correct sheet to look on. HTH –Sam

Reply | Quote

Thanks Sam
However, have some questions because I’m not fully understanding your example:

How does .Range know which sheet? Cell B1299 referred to is on a different sheet than Extra Notes.

The ExtraPage, ExtraComments and ExtraCollateral may not all be true — they are check boxes. Basically, we are wanting it to say that IF any of those boxes are checked OR if there is data in cell B1299, THEN we want it to print the extra notes (sheet).

Tracytext

Reply | Quote

If the cell is on a different sheet that that named in the With statement, then you need to specify the sheet, like this:

Option Explicit
Sub Macro3()
Dim ExtraPage As Boolean, ExtraComments As Boolean
Dim ExtraCollateral As Boolean
'   Next three lines replaced with your code
    ExtraPage = True
    ExtraComments = True
    ExtraCollateral = True
    With Sheets("Extra Notes")
        If ExtraPage Or ExtraComments Or ExtraCollateral _
        Or Worksheets("MySheet").Range("B1299")  "" Then _
            .PrintOut
    End With
End Sub

Reply | Quote

Oop – Sam
We figured it out.
Finally tied the VALUE of the cell to a variable, and named the variable in the code (instead of just the cell name) and it seems to work fine. Thanks for your help.

Reply | Quote

Thanks Legare, didn’t have time to post the correction.

Tracy, what do you mean, you “tied the VALUE of the cell to a variable?” How did you do that? (I actually want to know: sounds like a cool thing to do!) As Legare posted, Worksheets(“MySheet”).Range(“B1299”) “” does it, . Actually, since there are two different sheets, the with block dosn’t save anything, so I would replace the with block:

    If ExtraPage Or ExtraComments Or ExtraCollateral _
        Or Worksheets("MySheet").Range("B1299")  "" Then _
        Sheets("Extra Notes").PrintOut

Reply | Quote

To ‘tie’ a Value of a cell to a variable I simply assign a range name to the required cell e.g. “currentRate” (without the quotes) and then in VBA you can fetch this value simply with

rateValue = [currentRate]

zeddy

Reply | Quote