• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code streamlining

Skeeeter56

New Member
Hi all the following code prints 4 print jobs 1 after the other, this all works well.
I also have each of the 4 print jobs setup as a single print job.

I am sure there is a simpler way to do this using 1 block of code.
Code:
Private Sub cboPrint_Click()
Dim pwd     As String
pwd = "post"
Application.ScreenUpdating = False
 Sheets("NBC").Unprotect Password:=pwd
 Sheets("NBC110").Unprotect Password:=pwd
  
    
   'Printout for NBC110
    Worksheets("NBC").Range("H18").Value = Worksheets("NBC").Range("I9")   'Set this to First Row of Data sheet where NBC's start
    Worksheets("NBC").Range("H19").Value = Worksheets("NBC").Range("I10") 'Set this to Last Row of Data Sheet where NBC's End
    Worksheets("NBC110").Activate
    
    PrintFormsNBC
    
    Sheets("EVEE").Unprotect Password:=pwd
    Sheets("EVEES").Unprotect Password:=pwd
    
    'Printout for Evee's
    Worksheets("EVEE").Range("H18").Value = Worksheets("VAN").Range("I9")   'Set this to First Row of Data sheet where VAN's start
    Worksheets("EVEE").Range("H19").Value = Worksheets("VAN").Range("I10")   'Set this to Last Row of Data Sheet where VAN's End
    Worksheets("EVEES").Activate
    
    PrintFormsEVEES
    
    Sheets("VAN").Unprotect Password:=pwd
    Sheets("VANS").Unprotect Password:=pwd
    
    'Printout for Van's
    Worksheets("VAN").Range("H18").Value = Worksheets("VAN").Range("I9")   'Set this to First Row of Data sheet where VAN's start
    Worksheets("VAN").Range("H19").Value = Worksheets("VAN").Range("I10")   'Set this to Last Row of Data Sheet where VAN's End
    Worksheets("VANS").Activate
    
    PrintFormsVANS
  
   'Openform and await the OK button to resume
    
    ChangeNBCPaper.Show
    Sheets("EAMB").Unprotect Password:=pwd
    Sheets("EAMBS").Unprotect Password:=pwd
  
   'Printout for Eamb's
    Worksheets("EAMB").Range("H18").Value = Worksheets("EAMB").Range("I9")   'Set this to First Row of Data sheet where NBC's start
    Worksheets("EAMB").Range("H19").Value = Worksheets("EAMB").Range("I10")   'Set this to Last Row of Data Sheet where NBC's End
    Worksheets("EAMBS").Activate
    
    PrintFormsEAMB
    
    'Protect sheets
    Sheets("NBC").Protect Password:=pwd
    Sheets("NBC110").Protect Password:=pwd
    Sheets("EVEE").Protect Password:=pwd
    Sheets("EVEES").Protect Password:=pwd
    Sheets("VAN").Protect Password:=pwd
    Sheets("VANS").Protect Password:=pwd
    Sheets("EAMB").Protect Password:=pwd
    Sheets("EAMBS").Protect Password:=pwd
  
    Application.ScreenUpdating = True
    Worksheets("Menu").Activate
    
End Sub

Here is 1 of the single print jobs code
Code:
Private Sub CommandButton2_Click()

    
 Dim pwd     As String
    pwd = "post"
    Application.ScreenUpdating = False
 Sheets("NBC").Unprotect Password:=pwd
 Sheets("NBC110").Unprotect Password:=pwd
'Printout for NBC110
    Worksheets("NBC").Range("H18").Value = Worksheets("NBC").Range("I9")   'Set this to First Row of Data sheet where NBC's start
    Worksheets("NBC").Range("H19").Value = Worksheets("NBC").Range("I10") 'Set this to Last Row of Data Sheet where NBC's End
    Worksheets("NBC110").Activate
    
    PrintFormsNBC
    
    Sheets("NBC").Protect Password:=pwd
    Sheets("NBC110").Protect Password:=pwd
    Application.ScreenUpdating = True
    Worksheets("Menu").Activate
End Sub

I am sure this code can be made much better, it all works as is.
 
Back
Top