API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 10th, 2019
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 15.46 KB | None | 0 0
raw download clone embed print report
  1. Sub Button2_Click()
  2. Dim mySheets As Long
  3. Dim myActiveSheet As String
  4. Dim standardSheetNumber As Long
  5. Dim moduleStart As Long
  6.  
  7. Dim accountNumber As Long
  8. Dim Model As String
  9. Dim CompanyName As String
  10. Dim Address As String
  11. Dim City As String
  12. Dim State As String
  13. Dim ZipCode As String
  14. Dim fiancialInfo As String
  15. Dim leaseTerm As Long
  16. Dim appText As String
  17.  
  18. moduleStart = 12
  19. standardSheetNumber = 15
  20. mySheets = Worksheets.Count
  21.  
  22. 'Get account details
  23. accountName = Sheets(1).Cells(21, 2).Value
  24. accountNumber = Sheets(1).Cells(17, 2).Value
  25. accountAddress = Sheets(1).Cells(22, 2).Value
  26. accountCity = Sheets(1).Cells(24, 2).Value
  27. accountState = Sheets(1).Cells(26, 2).Value
  28. accountZip = Sheets(1).Cells(27, 2).Value
  29. accountContact = Sheets(1).Cells(30, 4).Value
  30. accountPhone = Sheets(1).Cells(28, 4).Value
  31. accountFax = Sheets(1).Cells(29, 4).Value
  32. accountEmail = Sheets(1).Cells(31, 4).Value
  33. accountRep = Sheets(1).Cells(12, 2).Value
  34. machine = 16
  35. machineQty = Sheets(2).Cells(machine, 27).Value
  36.  
  37.  
  38. 'App Text
  39. appText = "You apply to us to service the equipment listed above to you for the Initial Period referred to above and thereafter in accordance with the terms and conditions stated above and set out overleaf. You agree to pay to us the payments set forth above in accordance with the frequency set out above. You agree that all information set out herein is correct and that all particulars were complete when this application was signed. You acknowledge having read the terms and conditions of this Agreement set forth on this page and overleaf, and agree that no other terms and conditions, express or implied, are part of this agreement unless they appear above or in a schedule or addendum, and in either event are initialed by both of us to indicate they form part of this Agreement"
  40.  
  41.  
  42. 'Fill out Header
  43. Range(Cells(6, 2), Cells(6, 5)).Merge
  44. Range(Cells(6, 2), Cells(6, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  45. Range(Cells(7, 2), Cells(7, 5)).Merge
  46. Range(Cells(7, 2), Cells(7, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  47. Range(Cells(7, 7), Cells(8, 9)).Merge
  48. Range(Cells(8, 2), Cells(8, 5)).Merge
  49. Range(Cells(8, 2), Cells(8, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  50. Range(Cells(9, 2), Cells(9, 5)).Merge
  51. Range(Cells(9, 2), Cells(9, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  52. Range(Cells(10, 2), Cells(10, 5)).Merge
  53. Range(Cells(10, 2), Cells(10, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  54. Range(Cells(11, 2), Cells(11, 5)).Merge
  55. Range(Cells(11, 2), Cells(11, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  56. Range(Cells(12, 2), Cells(12, 5)).Merge
  57. Range(Cells(12, 2), Cells(12, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  58.  
  59.  
  60. Cells(6, 2).Value = accountName
  61. Cells(7, 2).Value = accountAddress
  62. Cells(8, 2).Value = accountCity & " - " & accountState & " - " & accountZip
  63. Cells(9, 2).Value = accountContact
  64. Cells(10, 2).Value = accountPhone
  65.  
  66. If accountNumber = 0 Then
  67.     Range(Cells(6, 7), Cells(6, 7)).Interior.ColorIndex = 6
  68. Else
  69.     Cells(6, 7).Value = accountNumber
  70. End If
  71.  
  72. If (accountFax = "") Then
  73.     Range(Cells(11, 2), Cells(11, 2)).Interior.ColorIndex = 6
  74. Else
  75.     Cells(11, 2).Value = accountFax
  76. End If
  77.  
  78. If accountEmail = "" Then
  79.     Range(Cells(12, 2), Cells(12, 2)).Interior.ColorIndex = 6
  80.     Range(Cells(7, 7), Cells(7, 7)).Interior.ColorIndex = 6
  81. Else
  82.     Cells(12, 2).Value = accountEmail
  83.     Cells(7, 7).Value = accountEmail
  84. End If
  85.  
  86. If accountRep = "" Then
  87.     Range(Cells(10, 7), Cells(10, 7)).Interior.ColorIndex = 6
  88. Else
  89.     Cells(10, 7).Value = accountRep
  90. End If
  91.  
  92.  
  93. 'Create Layout
  94. For i = standardSheetNumber To mySheets
  95.  
  96. Range(Cells(moduleStart + 1, 9), Cells(moduleStart + 20, 9)).RowHeight = 10.2
  97. Range(Cells(moduleStart + 1, 1), Cells(moduleStart + 20, 9)).Font.Bold = False
  98. Range(Cells(moduleStart + 1, 9), Cells(moduleStart + 20, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  99.  
  100. Cells(moduleStart + 2, 1).Value = "Contract Type: CPC"
  101. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Merge
  102. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Font.Bold = True
  103.  
  104. Cells(moduleStart + 3, 1).Value = "Model"
  105. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Merge
  106. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Font.Bold = True
  107.  
  108. Cells(moduleStart + 5, 1).Value = "Additional Fixed Charge"
  109. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Merge
  110. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
  111.  
  112. Cells(moduleStart + 6, 1).Value = "NO"
  113. Cells(moduleStart + 7, 1).Value = "Fixed Charge Description (if applicable):"
  114. Range(Cells(moduleStart + 7, 1), Cells(moduleStart + 7, 9)).Merge
  115.  
  116. 'Black
  117. Cells(moduleStart + 9, 1).Value = "Blk"
  118. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
  119. Range(Cells(moduleStart + 8, 1), Cells(moduleStart + 10, 9)).Borders.LineStyle = xlContinuous
  120.  
  121. 'Color
  122. Cells(moduleStart + 10, 1).Value = "Clr"
  123. Cells(moduleStart + 12, 1).Value = "SHIP TO:"
  124. Range(Cells(moduleStart + 12, 1), Cells(moduleStart + 12, 9)).Merge
  125. Cells(moduleStart + 12, 1).Font.Bold = True
  126.  
  127. Cells(moduleStart + 13, 1).Value = "Account #:"
  128.  
  129. Cells(moduleStart + 14, 1).Value = "Name"
  130. Cells(moduleStart + 15, 1).Value = "Address"
  131.  
  132. Cells(moduleStart + 19, 1).Value = "Special Provisions:"
  133. Range(Cells(moduleStart + 19, 1), Cells(moduleStart + 20, 8)).Merge
  134. Range(Cells(moduleStart + 19, 1), Cells(moduleStart + 20, 8)).VerticalAlignment = xlVAlignTop
  135. Range(Cells(moduleStart + 19, 1), Cells(moduleStart + 20, 8)).Borders.LineStyle = xlContinuous
  136.  
  137. Cells(moduleStart + 6, 2).Value = "YES"
  138.  
  139. 'Image Charge
  140. Cells(moduleStart + 8, 2).Value = "Image Charge"
  141. Cells(moduleStart + 9, 2).Value = Sheets(2).Cells(machine, 37).Value
  142. Cells(moduleStart + 10, 2).Value = Sheets(2).Cells(machine, 38).Value
  143. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 9)).Font.Bold = True
  144. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).HorizontalAlignment = xlCenter
  145. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).Merge
  146.  
  147. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).HorizontalAlignment = xlCenter
  148. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).Merge
  149.  
  150. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).HorizontalAlignment = xlCenter
  151. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).Merge
  152.  
  153. Cells(moduleStart + 3, 4).Value = "Serial#"
  154. Range(Cells(moduleStart + 4, 4), Cells(moduleStart + 4, 5)).Interior.ColorIndex = 6
  155. Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 5)).Merge
  156. Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 9)).Font.Bold = True
  157.  
  158. Cells(moduleStart + 5, 4).Value = "Fix Change Amount"
  159. Range(Cells(moduleStart + 5, 4), Cells(moduleStart + 5, 9)).Font.Bold = True
  160. Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 5)).Merge
  161. Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 8)).Interior.ColorIndex = 6
  162.  
  163. 'Agreed Volume
  164. Cells(moduleStart + 8, 5).Value = "Agreed Volume"
  165. Cells(moduleStart + 9, 5).Value = Sheets(2).Cells(machine, 39).Value
  166. Cells(moduleStart + 10, 5).Value = Sheets(2).Cells(machine, 40).Value
  167. Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 5)).HorizontalAlignment = xlCenter
  168. Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 7)).Merge
  169.  
  170. Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).HorizontalAlignment = xlCenter
  171. Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).Merge
  172.  
  173. Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 5)).HorizontalAlignment = xlCenter
  174. Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 7)).Merge
  175.  
  176. 'Installed date
  177. Cells(moduleStart + 3, 6).Value = "Installed Date"
  178. Range(Cells(moduleStart + 3, 6), Cells(moduleStart + 3, 8)).Merge
  179. Range(Cells(moduleStart + 4, 6), Cells(moduleStart + 4, 8)).Merge
  180.  
  181. Cells(moduleStart + 5, 6).Value = "Billing Frequency"
  182. Range(Cells(moduleStart + 5, 6), Cells(moduleStart + 5, 8)).Merge
  183. Range(Cells(moduleStart + 6, 6), Cells(moduleStart + 6, 8)).Merge
  184.  
  185. Cells(moduleStart + 13, 6).Value = "Meter Read:"
  186. Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Merge
  187. Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  188. Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 8)).Interior.ColorIndex = 6
  189.  
  190. Cells(moduleStart + 14, 6).Value = "Phone #:"
  191. Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Merge
  192. Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  193.  
  194. Cells(moduleStart + 15, 6).Value = "Fax #:"
  195. Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Merge
  196. Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  197.  
  198. Cells(moduleStart + 16, 6).Value = "Email:"
  199. Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Merge
  200. Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  201.  
  202. 'Cells at bottom of Module
  203. Range(Cells(moduleStart + 17, 7), Cells(moduleStart + 17, 9)).Merge
  204. Range(Cells(moduleStart + 18, 1), Cells(moduleStart + 18, 9)).Merge
  205.  
  206. Cells(moduleStart + 8, 8).Value = "Meter Start"
  207. Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 8)).HorizontalAlignment = xlCenter
  208. Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 9)).Merge
  209.  
  210. 'under meter start
  211. Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 8)).HorizontalAlignment = xlCenter
  212. Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 9)).Merge
  213.  
  214. 'second cell under meter start
  215. Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 8)).HorizontalAlignment = xlCenter
  216. Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 9)).Merge
  217.  
  218. Cells(moduleStart + 3, 9).Value = "Service Fee"
  219.  
  220. Cells(moduleStart + 4, 9).Interior.ColorIndex = 6
  221.  
  222. Cells(moduleStart + 5, 9).Value = "Initial Period (Mths)"
  223.  
  224. Cells(moduleStart + 6, 9).HorizontalAlignment = xlLeft
  225.  
  226. 'line break the customer and initial
  227. Cells(moduleStart + 19, 9).Value = "Customer " & Chr(10) & "Initial:"
  228. Range(Cells(moduleStart + 19, 9), Cells(moduleStart + 20, 9)).Merge
  229. Range(Cells(moduleStart + 19, 9), Cells(moduleStart + 20, 9)).Borders.LineStyle = xlContinuous
  230.  
  231.  
  232.     'Get Sheet name
  233.  myActiveSheet = Sheets(i).Name
  234.  
  235.     'Get Model
  236.  Range(Cells(moduleStart + 4, 1), Cells(moduleStart + 4, 3)).Merge
  237.     Model = Sheets(myActiveSheet).Cells(16, 2).Value
  238.     Cells(moduleStart + 4, 1).Value = Model
  239.    
  240.     'insert Account number
  241.  Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Merge
  242.     Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  243.     Cells(moduleStart + 13, 2).Value = accountNumber
  244.     Cells(moduleStart + 13, 2).HorizontalAlignment = xlLeft
  245.    
  246.     'Get CompanyName from sheet
  247.  Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Merge
  248.     Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  249.     CompanyName = Sheets(myActiveSheet).Cells(7, 2).Value
  250.     Cells(moduleStart + 14, 2).Value = CompanyName
  251.    
  252.     'Get Address from sheet
  253.  Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Merge
  254.     Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  255.     Address = Sheets(myActiveSheet).Cells(8, 2).Value
  256.     Cells(moduleStart + 15, 2).Value = Address
  257.    
  258.     'Get City from sheet
  259.  Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Merge
  260.     Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  261.     City = Sheets(myActiveSheet).Cells(9, 2).Value
  262.     Cells(moduleStart + 16, 2).Value = City
  263.    
  264.     'Get State from sheet
  265.  State = Sheets(myActiveSheet).Cells(10, 2).Value
  266.    
  267.     'Get ZipCode from sheet
  268.  Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Merge
  269.     Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  270.     ZipCode = Sheets(myActiveSheet).Cells(11, 2).Value
  271.     Cells(moduleStart + 17, 2).Value = State & " - " & ZipCode
  272.    
  273.     'Get Lease Term  (serice term always 12)
  274.    'leaseTerm = Sheets(3).Cells(15, 4).Value
  275.    Cells(moduleStart + 6, 9).Value = 12
  276.    
  277.     moduleStart = moduleStart + 20
  278.    
  279.     If machineQty <= 1 Then
  280.         machine = machine + 1
  281.     End If
  282.    
  283.     machineQty = machineQty - 1
  284.    
  285.     If machineQty = 0 Then
  286.         machineQty = Sheets(2).Cells(machine, 25).Value
  287.     End If
  288.    
  289.    
  290. Next i
  291.  
  292. 'Insert Footer
  293.  
  294. 'Application
  295. Range(Cells(moduleStart + 1, 1), Cells(moduleStart + 1, 9)).Merge
  296. Cells(moduleStart + 1, 1).Value = "APPLICATION:" & Chr(10) & appText
  297. Cells(moduleStart + 1, 1).Font.Size = 6.5
  298. Cells(moduleStart + 1, 1).Font.Bold = False
  299. Cells(moduleStart + 1, 1).Characters(1, 12).Font.Bold = True
  300. Cells(moduleStart + 1, 1).Characters(1, 12).Font.Size = 7.5
  301. Cells(moduleStart + 1, 1).RowHeight = 72.6
  302. Cells(moduleStart + 1, 1).WrapText = True
  303. Cells(moduleStart + 1, 1).IndentLevel = 1
  304. Range(Cells(moduleStart + 1, 1), Cells(moduleStart + 1, 9)).VerticalAlignment = xlCenter
  305. Range(Cells(moduleStart + 1, 9), Cells(moduleStart + 6, 9)).Borders(xlEdgeRight).LineStyle = xlContinuous
  306.  
  307. 'Signature
  308. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Merge
  309. Cells(moduleStart + 2, 1).Value = "SIGNATURE"
  310. Cells(moduleStart + 2, 1).Font.Bold = True
  311. Cells(moduleStart + 2, 1).IndentLevel = 1
  312. Cells(moduleStart + 2, 1).RowHeight = 14.4
  313. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 5, 9)).Borders.LineStyle = xlContinuous
  314.  
  315. 'Signatures of Customers
  316. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 8)).Merge
  317. Cells(moduleStart + 3, 1).Value = "Signaure(s) of Customer(s)"
  318. Cells(moduleStart + 3, 1).IndentLevel = 33
  319. Cells(moduleStart + 3, 1).Font.Bold = False
  320.  
  321. 'Acceptance
  322. Cells(moduleStart + 3, 9).Value = "Acceptance by Document Direction Limited"
  323. Cells(moduleStart + 3, 9).Font.Size = 6.5
  324. Cells(moduleStart + 3, 9).Font.Bold = False
  325.  
  326. 'SignatureField2
  327. Range(Cells(moduleStart + 4, 1), Cells(moduleStart + 4, 3)).Merge
  328. Cells(moduleStart + 4, 1).Value = "Signature"
  329. Cells(moduleStart + 4, 1).Font.Size = 7.5
  330. Cells(moduleStart + 4, 1).Font.Bold = False
  331. Cells(moduleStart + 4, 1).HorizontalAlignment = xlCenter
  332.  
  333. 'Print name and Position
  334. Range(Cells(moduleStart + 4, 4), Cells(moduleStart + 4, 6)).Merge
  335. Cells(moduleStart + 4, 4).Value = "Print name and Position"
  336. Cells(moduleStart + 4, 4).Font.Size = 7.5
  337. Cells(moduleStart + 4, 4).Font.Bold = False
  338. Cells(moduleStart + 4, 4).HorizontalAlignment = xlCenter
  339.  
  340. 'Date Signed
  341. Range(Cells(moduleStart + 4, 7), Cells(moduleStart + 4, 8)).Merge
  342. Cells(moduleStart + 4, 7).Value = "Date Signed"
  343. Cells(moduleStart + 4, 7).Font.Size = 7.5
  344. Cells(moduleStart + 4, 7).Font.Bold = False
  345. Cells(moduleStart + 4, 7).HorizontalAlignment = xlCenter
  346.  
  347. 'Signature of Document Direction Limited
  348. Cells(moduleStart + 4, 9).Value = "Signature of Document Direction Limited"
  349. Cells(moduleStart + 4, 9).Font.Size = 7.5
  350. Cells(moduleStart + 4, 9).Font.Bold = False
  351. Cells(moduleStart + 4, 7).HorizontalAlignment = xlCenter
  352.  
  353. 'Signature Field
  354. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Merge
  355. Cells(moduleStart + 5, 1).RowHeight = 46.8
  356.  
  357. 'Print name and Position
  358. Range(Cells(moduleStart + 5, 4), Cells(moduleStart + 5, 6)).Merge
  359.  
  360. 'Date signed
  361. Range(Cells(moduleStart + 5, 7), Cells(moduleStart + 5, 8)).Merge
  362.  
  363. 'Service start date
  364. Cells(moduleStart + 5, 9).Font.Bold = False
  365. Cells(moduleStart + 5, 9).Value = "Service Start Date:"
  366. Cells(moduleStart + 5, 9).VerticalAlignment = xlBottom
  367. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!