Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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=

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

    You want to use the Range method. Here's how I would do it:
    <pre>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</pre>

    Notice that there is a period before Range, so that Excel knows the correct sheet to look on. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

    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

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

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

    <pre>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
    </pre>

    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

    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:
    <pre> If ExtraPage Or ExtraComments Or ExtraCollateral _
    Or Worksheets("MySheet").Range("B1299") <> "" Then _
    Sheets("Extra Notes").PrintOut</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Excel/VBA Don't print if cell is empty (Excel 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •