none
VBA PRINT PROBLEM! RRS feed

  • General discussion

  • Hi,

    I'm having a problem with VBA code, and I've tried to think of every work around to rectify the problem.

    We have an excel spreadsheet with lines filled from other sheets within the same workbook, but when I try and get the command to print, it will only do so once all the cells refered to are filled, and will not if any of the cells are empty.

    The code I have is:

    Private Sub CommandButton38_Click()

    'RESET FUNCTION
    RESET
    'COLOUR CELLS TO CONFIRM PAGE IS PRINTED
    Range("Q38:Q41").Select
    With Selection.Interior
    .ColorIndex = 39
    End With
    'LINE NUMBERS
    Sheets("AS").Range("V2").Value = Sheets("AS").Range("Q38").Value
    Sheets("AS").Range("V3").Value = Sheets("AS").Range("Q39").Value
    Sheets("AS").Range("V4").Value = Sheets("AS").Range("Q40").Value
    Sheets("AS").Range("V5").Value = Sheets("AS").Range("Q41").Value
    'DRAWING FUNCTION
    DRAWINGS
    'PRINT PDF everything works fine upto this point.... but if V4 is empty then it won't print the sheet.I've set the print area in the 'drawings' function
    PRINTPDF

    End Sub

    ----------------------------------------------------------

    Function PRINTPDF()

    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Function

    ----------------------------------------------------------

    Now I'm sure I've done something incorrect, but can't see the problem!

    Can someone help?

    Regards,

    Harvey

    Thursday, August 29, 2013 8:46 AM

All replies

  • How does your code set the print area in the 'drawings' function?

    Perhaps fill in empty cells - for each of your assignments above, add a check

    'Line from above

    Sheets("AS").Range("V2").Value = Sheets("AS").Range("Q38").Value

    'Added check for that assignment

    If Sheets("AS").Range("V2").Value = "" Then Sheets("AS").Range("V2").Value = " "

    Friday, August 30, 2013 6:09 PM