Please advise on my IF code

[ipbr21054]

Below is part of my full code.
It should print an invoice then ask if it printed ok.
If YES then continue to do the part in Red.

BUT if NO then print again & then do the part in RED

Currently both the print options work BUT the part in Red does nothing

    With ActiveSheet
      'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE NOW PRINTED
      MsgBox "1st Print"
    End With
      answer = MsgBox("INVOICE HAS NOW BEEN SAVED" & vbNewLine & vbNewLine & "DID THE INVOICE PRINT OK FOR YOU ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
    If answer = vbNo Then
      'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE PRINTED AGAIN IF FIRST PRINT WAS POOR
      MsgBox "2nd Print"
      Range("L4").Value = Range("L4").Value + 1
      Range("G27:L36").ClearContents
      Range("G46:G50").ClearContents
      Range("L18").ClearContents
      Range("G13").ClearContents
      Range("G13").Select
      ActiveWorkbook.Save
    End If
       
    End With

    End Sub

 

[ranman256]

you have a WITH, but not using it.
maybe with the 'dot' :

      .Range("L4").Value = Range("L4").Value + 1
      .Range("G27:L36").ClearContents
      .Range("G46:G50").ClearContents
      .Range("L18").ClearContents
     .Range("G13").ClearContents
      .Range("G13").Select

 

[6StringJazzer]

Also you are doing the code in red only when the answer is NO. To always do it, move the End If up above the red code.

 

[ipbr21054]

I don’t understand.
Please put the code here

 

[MARK858]

The With is ended here (although the With statement doesn't do anything and the End With without a With)

      MsgBox "1st Print"
    End With

There is an End With near the end that doesn't have a With

This works for me (I have removed the With that isn't doing anything)

Sub xxxx()
    Dim answer As Long
 
        'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE NOW PRINTED
        MsgBox "1st Print"
 
  
    answer = MsgBox("INVOICE HAS NOW BEEN SAVED" & vbNewLine & vbNewLine & "DID THE INVOICE PRINT OK FOR YOU ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
  
    If answer = vbNo Then
        'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE PRINTED AGAIN IF FIRST PRINT WAS POOR
        MsgBox "2nd Print"
        Range("L4").Value = Range("L4").Value + 1
        Range("G27:L36").ClearContents
        Range("G46:G50").ClearContents
        Range("L18").ClearContents
        Range("G13").ClearContents
        Range("G13").Select
        ActiveWorkbook.Save
    End If
     


End Sub

 

[6StringJazzer]

OP said

If YES then continue to do the part in Red.

BUT if NO then print again & then do the part in RED

Code in red required for either condition.

MARK858's code updated with this requirement:

Sub xxxx()
    Dim answer As Long
 
        'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE NOW PRINTED
        MsgBox "1st Print"
 
  
    answer = MsgBox("INVOICE HAS NOW BEEN SAVED" & vbNewLine & vbNewLine & "DID THE INVOICE PRINT OK FOR YOU ?", vbInformation + vbYesNo, "INVOICE PRINT OK MESSAGE")
  
    If answer = vbNo Then
        'ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE PRINTED AGAIN IF FIRST PRINT WAS POOR
        MsgBox "2nd Print"
   End If


   Range("L4").Value = Range("L4").Value + 1
   Range("G27:L36").ClearContents
   Range("G46:G50").ClearContents
   Range("L18").ClearContents
   Range("G13").ClearContents
   Range("G13").Select
   ActiveWorkbook.Save

End Sub

 

[ipbr21054]

Hi,
Thanks for the reply but but just continued to run through all the code.
As a result i have had to change it to just one print

Thanks

 

[MARK858]

Are you sure that your code is doing that (i.e. you have stepped through the code with F8) because it doesn't for me, and you are looking at the Activesheet.

If you still think that can you please post your full code from the line beginning Sub to the line End Sub

 

[ipbr21054]

Here is the whole code just incase its something up the field a bit.

It all works fine now so the issue was basically printing


The idea was to print the sheet & ask whether it printed OK
If YES then clear contents.
BUT
If NO then print again & then clear contents without the message
The code supplied is with what im running so deleted what was there using the advice given but you will see that & change it etc.

Thanks

Private Sub Print_Invoice_Click()
    Dim answer As Integer
    Dim rng As Range
    Dim cell As Range
    Dim MyFile As String
    Dim findString As String
    Dim sPath As String, strFileName As String
    Dim srcWS As Worksheet, destWS As Worksheet
    Set srcWS = ActiveWorkbook.Worksheets("INV")
    Set destWS = ActiveWorkbook.Worksheets("DATABASE")
    
    With ActiveSheet
    If Range("G13") = "" Then
      MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
      Range("G13").Select 'CHECKING IF CUSTOMER IS SELECTED
    Exit Sub
    End If
  
    If Range("L18") = "" Then
      MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
      Range("L18").Select 'CHECKING IF PAYMENT TYPE HAS BEEN SELECTED
    Exit Sub
    End If
        
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
    If Dir(strFileName) <> vbNullString Then
      MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS" & vbNewLine & vbNewLine & "PLEASE CHECK FILE IN FOLDER THAT WILL NOW OPEN.", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
      VBA.Shell "explorer.exe /select, " & "" & strFileName & "", vbNormalFocus 'DUPLICATE INVOICE FOUND
    Exit Sub
    End If
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
    With ActiveSheet
      .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End With 'CURRENT INVOICE IS NOW SAVED
    
    With ActiveSheet
      .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End With 'CURRENT INVOICE IS NOW SAVED
    
    With Sheets("DATABASE")
      Worksheets("DATABASE").Activate
    End With
    
    Set rng = ActiveSheet.Columns("A:A")
      findString = Worksheets("INV").Range("G13").Value
    Set cell = rng.Find(What:=findString, LookIn:=xlFormulas, _
      LookAt:=xlWhole, MatchCase:=False) ' CUSTOMER FOUND IN COLUMN A
    
    If cell Is Nothing Then
      MsgBox "NO CUSTOMER WAS FOUND"
    Else
    With Sheets("DATABASE")
      cell.Select
      ActiveCell.Offset(0, 15).Select ' CUSTOMERS CELL IN COLUMN P NOW SELECTED
    End With
    End If
    
    If Len(ActiveCell.Value) <> 0 Then
      ValueInInvoiceCell.Show 'MESSAGE SHOWN IF CUSTOMERS INVOICE CELL IN COLUMN P HAS A VALUE IN IT

    Exit Sub
    Else
      TransferInvoiceNumber.Show 'NOW ENTER INVOICE NUMBER IN CUSTOMERS CELL IN COLUMN P & NOW HYPERLINKED
    End If
    
    With Sheets("DATABASE")
      Worksheets("INV").Activate 'WORKSHEET INVOICE HAS NOW BEEN ACTIVATED
      End With
    With ActiveSheet
      ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE NOW PRINTED
      
      MsgBox "INVOICE HAS NOW BEEN SAVED", vbInformation, "INVOICE PRINTED MESSAGE"
    
      Range("L4").Value = Range("L4").Value + 1
      Range("G27:L36").ClearContents
      Range("G46:G50").ClearContents
      Range("L18").ClearContents
      Range("G13").ClearContents
      Range("G13").Select
      ActiveWorkbook.Save
    
    End With
    End With
    
    End Sub

 

[MARK858]

It all works fine now so the issue was basically printing

I assume that this means that you no longer need any help

Just a few suggestions with your code...

There are a lot of With / End With's in your code that aren't linked to any range and so aren't doing anything.

You set variables to the worksheets but you use them only once, instead choosing to select the sheets and then use ActiveSheet throughout the rest of the code.
This a) slows the code down b) makes it a lot harder to debug as you have to work backwards to work out which sheet it is referring to

A prime example of both of the above is near the end of your code

    With Sheets("DATABASE")
        Worksheets("INV").Activate               'WORKSHEET INVOICE HAS NOW BEEN ACTIVATED
    End With
    
    With ActiveSheet
        ActiveWindow.SelectedSheets.PrintOut copies:=1 'INVOICE NOW PRINTED
      
        MsgBox "INVOICE HAS NOW BEEN SAVED", vbInformation, "INVOICE PRINTED MESSAGE"
    
        Range("L4").Value = Range("L4").Value + 1
        Range("G27:L36").ClearContents
        Range("G46:G50").ClearContents
        Range("L18").ClearContents
        Range("G13").ClearContents
        Range("G13").Select
        ActiveWorkbook.Save
        
    End With

The With / End With in blue isn't linked to any range and so is doing nothing
In Red.. Worksheets("INV") is the variable srcWS and again the With / End With isn't linked to any range and so is doing nothing

That part of your code could be

    ActiveWindow.SelectedSheets.PrintOut copies:=1 'This you would need to look at, possibly just srcWS.PrintOut copies:=1 INVOICE NOW PRINTED
      
    MsgBox "INVOICE HAS NOW BEEN SAVED", vbInformation, "INVOICE PRINTED MESSAGE"
    
    With srcWS
        .Range("L4").Value = .Range("L4").Value + 1
        .Range("G27:L36,G46:G50,L18,G13").ClearContents
        Application.Goto .Range("G13")
    End With
   
    ActiveWorkbook.Save