Private Sub Workbook_Open()
'Hide user interface when the workbook is opened
Call UIHide
'Pause screen updating
Application.ScreenUpdating = False
'Clear the contents of selected cells
Sheet1.Range("E7,E8").Value = ""
Sheet1.Range("E12,E13,E14").Value = ""
Sheet1.Range("E18,E19,E20,E21").Value = ""
Sheet6.Range("K7,M7").Value = ""
Sheet5.Range("B6,D6,G6,I6").Value = ""
Sheet5.Range("B14,D14,G14,I14").Value = ""
Sheet5.Range("B23,D23,G23,I23").Value = ""
Sheet5.Range("B31,D31,G31,I31").Value = ""
Sheet4.Range("B6,D6,G6,I6").Value = ""
Sheet4.Range("B14,D14,G14,I14").Value = ""
Sheet4.Range("B36,D36,G36,I36").Value = ""
Sheet4.Range("B44,D44,G44,I44").Value = ""
Sheet21.Range("A7,C7,P7,R7").Value = ""
Sheet21.Range("G9,I9,N9,P9").Value = ""
Sheet21.Range("A19,C19,P19,R19").Value = ""
Sheet21.Range("A25,C25,P25,R25").Value = ""
Sheet21.Range("G27,I27,N27,P27").Value = ""
Sheet21.Range("A37,C37,P37,R37").Value = ""
Sheet3.Range("D7,F7,O7,Q7").Value = ""
Sheet3.Range("B9,D9,I9,K9").Value = ""
Sheet3.Range("D19,F19,O19,Q19").Value = ""
Sheet3.Range("D25,F25,O25,Q25").Value = ""
Sheet3.Range("B27,D27,I27,K27").Value = ""
Sheet3.Range("B37,D37,O37,Q37").Value = ""
Sheet31.Range("A7,C7,Q7,S7").Value = ""
Sheet31.Range("C9,E9,H9,J9").Value = ""
Sheet31.Range("A19,C19,Q19,S19").Value = ""
Sheet31.Range("A25,C25,Q25,S25").Value = ""
Sheet31.Range("C27,E27,H27,J27").Value = ""
Sheet31.Range("A37,C37,Q37,S37").Value = ""
Sheet11.Range("F8,J8,M8,P8").Value = ""
Sheet11.Range("F9,J9,M9,P9").Value = ""
'Set default values
Sheet6.Range("T17").Value = 51
Sheet4.Range("Z33").Value = 40.25
Sheet3.Range("AE39").Value = 12
Sheet3.Range("AE40").Value = 9
Sheet21.Range("AF39").Value = 12
Sheet21.Range("AF40").Value = 9
Sheet31.Range("AG39").Value = 12
Sheet31.Range("AG40").Value = 9
Sheet11.Range("W43").Value = 45
Sheet8.Range("AB6").Value = 0.0359
Sheet8.Range("AB7").Value = 0.0598
Sheet8.Range("AB8").Value = 0.1196
'Restore screen updating
Application.ScreenUpdating = True
'Set calculation method to automatic mode
Application.Calculation = xlAutomatic
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Test if exit button was pressed accidentally before saving data
If MsgBox("Exit now?" & vbCr & "(any unsaved data will be lost)", vbQuestion + vbYesNo + vbDefaultButton2, "Exit ") = vbNo Then
'Cancel the call to close the application if user inputs "no"
Cancel = True
Else
'Suppress excel alert messages
Application.DisplayAlerts = False
'Set save status to true
ThisWorkbook.Saved = True
'Close application
Application.Quit
End If
End Sub
[Module 1]
Sub UIHide()
'Hide elements of the standard excel user interface that are not needed
With Application
.WindowState = xlMaximized
.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",False)"
.CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.DisplayStatusBar = True
.DisplayScrollBars = False
.DisplayFormulaBar = False
End With
'Hide elements of the active window that are not needed
With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayHeadings = False
.DisplayRuler = False
.DisplayFormulas = False
.DisplayGridlines = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub
Sub UIShow()
'Restore standard excel configurations when the workbook is closed
With Application
.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",True)"
.DisplayStatusBar = True
.DisplayScrollBars = True
.DisplayFormulaBar = True
End With
'Restore standard window configurations when the workbook is closed
With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayRuler = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub
Sub todays_date()
'Enter todays date into the date field
Range("E14").Value = Date
End Sub
Sub clear_all()
'Declare variables
Dim answerA As Integer
'Test if clear button was pressed accidentally before deleting data
answerA = MsgBox("Clear data from all pages?", vbQuestion + vbYesNo + vbDefaultButton2, "Clear All")
If answerA = vbNo Then
Exit Sub
End If
'Clear the contents of selected cells
Sheet1.Range("E7,E8").Value = ""
Sheet1.Range("E12,E13,E14").Value = ""
Sheet1.Range("E18,E19,E20,E21").Value = ""
Sheet6.Range("K7,M7").Value = ""
Sheet5.Range("B6,D6,G6,I6").Value = ""
Sheet5.Range("B14,D14,G14,I14").Value = ""
Sheet5.Range("B23,D23,G23,I23").Value = ""
Sheet5.Range("B31,D31,G31,I31").Value = ""
Sheet4.Range("B6,D6,G6,I6").Value = ""
Sheet4.Range("B14,D14,G14,I14").Value = ""
Sheet4.Range("B36,D36,G36,I36").Value = ""
Sheet4.Range("B44,D44,G44,I44").Value = ""
Sheet21.Range("A7,C7,P7,R7").Value = ""
Sheet21.Range("G9,I9,N9,P9").Value = ""
Sheet21.Range("A19,C19,P19,R19").Value = ""
Sheet21.Range("A25,C25,P25,R25").Value = ""
Sheet21.Range("G27,I27,N27,P27").Value = ""
Sheet21.Range("A37,C37,P37,R37").Value = ""
Sheet3.Range("D7,F7,O7,Q7").Value = ""
Sheet3.Range("B9,D9,I9,K9").Value = ""
Sheet3.Range("D19,F19,O19,Q19").Value = ""
Sheet3.Range("D25,F25,O25,Q25").Value = ""
Sheet3.Range("B27,D27,I27,K27").Value = ""
Sheet3.Range("B37,D37,O37,Q37").Value = ""
Sheet31.Range("A7,C7,Q7,S7").Value = ""
Sheet31.Range("C9,E9,H9,J9").Value = ""
Sheet31.Range("A19,C19,Q19,S19").Value = ""
Sheet31.Range("A25,C25,Q25,S25").Value = ""
Sheet31.Range("C27,E27,H27,J27").Value = ""
Sheet31.Range("A37,C37,Q37,S37").Value = ""
Sheet11.Range("F8,J8,M8,P8").Value = ""
Sheet11.Range("F9,J9,M9,P9").Value = ""
End Sub
Sub copy_T_O()
'Copy intial data entries to the final fields
Range("B23").Value = Range("B6").Value
Range("D23").Value = Range("D6").Value
Range("G23").Value = Range("G6").Value
Range("I23").Value = Range("I6").Value
Range("B31").Value = Range("B14").Value
Range("D31").Value = Range("D14").Value
Range("G31").Value = Range("G14").Value
Range("I31").Value = Range("I14").Value
End Sub
Sub default_A_CC_parameter()
'Restore default
Range("T17").Value = 51
End Sub
Sub print_setup_dialog()
'Open print dialog box to print report
Application.Dialogs(xlDialogPrint).Show
End Sub
Sub choose_directory()
'Declare variables
Dim path As String
Dim folder As FileDialog
'Prompt user to select a save directory with a dialog box
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
With folder
.Title = "Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button
path = .SelectedItems(1)
End With
'Display chosen directory path in the report sheet interface
Range("M3").Value = path
End Sub
Sub suggest_file_name()
'Declare variables
Dim customer As String
Dim WO As String
'Reference customer name and work order number
customer = Sheet1.Range("E7")
WO = Sheet1.Range("E8")
'Combine the customer name and alignment number into a suggested file name
Sheet2.Range("M5") = customer & " - " & WO
End Sub
Sub save_data()
'Declare variables
Dim d(122) As String
Dim fname As String
Dim directory As String
Dim test As String
Dim answer As Integer
Dim folder As String
Dim fso As Object
Dim streamout As Object
Dim n As Integer
'Job data entries into data array
d(0) = Sheet1.Range("E7")
d(1) = Sheet1.Range("E8")
d(2) = Sheet1.Range("E12")
d(3) = Sheet1.Range("E13")
d(4) = Sheet1.Range("E14")
d(5) = Sheet1.Range("E18")
d(6) = Sheet1.Range("E19")
d(7) = Sheet1.Range("E20")
d(8) = Sheet1.Range("E21")
d(9) = Sheet6.Range("K7")
d(10) = Sheet6.Range("M7")
d(11) = Sheet5.Range("B6")
d(12) = Sheet5.Range("D6")
d(13) = Sheet5.Range("G6")
d(14) = Sheet5.Range("I6")
d(15) = Sheet5.Range("B14")
d(16) = Sheet5.Range("D14")
d(17) = Sheet5.Range("G14")
d(18) = Sheet5.Range("I14")
d(19) = Sheet5.Range("B23")
d(20) = Sheet5.Range("D23")
d(21) = Sheet5.Range("G23")
d(22) = Sheet5.Range("I23")
d(23) = Sheet5.Range("B31")
d(24) = Sheet5.Range("D31")
d(25) = Sheet5.Range("G31")
d(26) = Sheet5.Range("I31")
d(27) = Sheet4.Range("B6")
d(28) = Sheet4.Range("D6")
d(29) = Sheet4.Range("G6")
d(30) = Sheet4.Range("I6")
d(31) = Sheet4.Range("B14")
d(32) = Sheet4.Range("D14")
d(33) = Sheet4.Range("G14")
d(34) = Sheet4.Range("I14")
d(35) = Sheet4.Range("B36")
d(36) = Sheet4.Range("D36")
d(37) = Sheet4.Range("G36")
d(38) = Sheet4.Range("I36")
d(39) = Sheet4.Range("B44")
d(40) = Sheet4.Range("D44")
d(41) = Sheet4.Range("G44")
d(42) = Sheet4.Range("I44")
d(43) = Sheet21.Range("A7")
d(44) = Sheet21.Range("C7")
d(45) = Sheet21.Range("P7")
d(46) = Sheet21.Range("R7")
d(47) = Sheet21.Range("G9")
d(48) = Sheet21.Range("I9")
d(49) = Sheet21.Range("N9")
d(50) = Sheet21.Range("P9")
d(51) = Sheet21.Range("A19")
d(52) = Sheet21.Range("C19")
d(53) = Sheet21.Range("P19")
d(54) = Sheet21.Range("R19")
d(55) = Sheet21.Range("A25")
d(56) = Sheet21.Range("C25")
d(57) = Sheet21.Range("P25")
d(58) = Sheet21.Range("R25")
d(59) = Sheet21.Range("G27")
d(60) = Sheet21.Range("I27")
d(61) = Sheet21.Range("N27")
d(62) = Sheet21.Range("P27")
d(63) = Sheet21.Range("A37")
d(64) = Sheet21.Range("C37")
d(65) = Sheet21.Range("P37")
d(66) = Sheet21.Range("R37")
d(67) = Sheet3.Range("D7")
d(68) = Sheet3.Range("F7")
d(69) = Sheet3.Range("O7")
d(70) = Sheet3.Range("Q7")
d(71) = Sheet3.Range("B9")
d(72) = Sheet3.Range("D9")
d(73) = Sheet3.Range("I9")
d(74) = Sheet3.Range("K9")
d(75) = Sheet3.Range("D19")
d(76) = Sheet3.Range("F19")
d(77) = Sheet3.Range("O19")
d(78) = Sheet3.Range("Q19")
d(79) = Sheet3.Range("D25")
d(80) = Sheet3.Range("F25")
d(81) = Sheet3.Range("O25")
d(82) = Sheet3.Range("Q25")
d(83) = Sheet3.Range("B27")
d(84) = Sheet3.Range("D27")
d(85) = Sheet3.Range("I27")
d(86) = Sheet3.Range("K27")
d(87) = Sheet3.Range("B37")
d(88) = Sheet3.Range("D37")
d(89) = Sheet3.Range("O37")
d(90) = Sheet3.Range("Q37")
d(91) = Sheet31.Range("A7")
d(92) = Sheet31.Range("C7")
d(93) = Sheet31.Range("Q7")
d(94) = Sheet31.Range("S7")
d(95) = Sheet31.Range("C9")
d(96) = Sheet31.Range("E9")
d(97) = Sheet31.Range("H9")
d(98) = Sheet31.Range("J9")
d(99) = Sheet31.Range("A19")
d(100) = Sheet31.Range("C19")
d(101) = Sheet31.Range("Q19")
d(102) = Sheet31.Range("S19")
d(103) = Sheet31.Range("A25")
d(104) = Sheet31.Range("C25")
d(105) = Sheet31.Range("Q25")
d(106) = Sheet31.Range("S25")
d(107) = Sheet31.Range("C27")
d(108) = Sheet31.Range("E27")
d(109) = Sheet31.Range("H27")
d(110) = Sheet31.Range("J27")
d(111) = Sheet31.Range("A37")
d(112) = Sheet31.Range("C37")
d(113) = Sheet31.Range("Q37")
d(114) = Sheet31.Range("S37")
d(115) = Sheet11.Range("F8")
d(116) = Sheet11.Range("J8")
d(117) = Sheet11.Range("M8")
d(118) = Sheet11.Range("P8")
d(119) = Sheet11.Range("F9")
d(120) = Sheet11.Range("J9")
d(121) = Sheet11.Range("M9")
d(122) = Sheet11.Range("P9")
'Reference the directory chosen by the user
directory = Sheet2.Range("M3").Value
'If a directory was not entered, exit the subroutine with a message
If directory = "" Then
MsgBox ("Data file not written. Please choose a directory.")
Exit Sub
End If
'Evaluate the folders existence in the directory
folder = Dir(directory, vbDirectory)
'If the folder does not exist in the choosen directoy, exit the subroutine with a message
If folder = vbNullString Then
MsgBox ("Data file not written. Please choose a valid directory.")
Exit Sub
End If
'Reference the file name chosen by the user
fname = Sheet2.Range("M5").Value
'If a file name was not entered, exit the subroutine with a message
If fname = "" Then
MsgBox ("Data file not written. Please enter a file name.")
Exit Sub
End If
'If the file name contains illegal characters, exit the subroutine with a message
If IsValidFileName(fname) Then
Else
MsgBox ("Data file not written. Please enter a valid file name.")
Exit Sub
End If
'Test if the file already exists and if it does, ask the user if they want to over write it
test = Dir(directory & "" & fname & ".txt")
If test <> vbNullString Then
answer = MsgBox("Overwrite File?", vbQuestion + vbYesNo + vbDefaultButton2, "File Already Exists")
If answer = vbNo Then
Exit Sub
End If
End If
'Create a new file system object
Set fso = CreateObject("Scripting.FileSystemObject")
'Create a new file with the user selected name and directory and open it for writing
Set streamout = fso.CreateTextFile(directory & "" & fname & ".txt")
'Write each data point from the array into a separate line of the new text file
For n = 0 To 122
streamout.WriteLine (d(n))
Next
'Close the newly created text file
streamout.Close
'Inform user if the file was created successfully
If fso.FileExists(directory & "" & fname & ".txt") Then
MsgBox "Data file written."
Else
MsgBox "Data file not written. Please check file name and directory."
End If
End Sub
Sub open_data()
'Declare variables
Dim fopen As FileDialog
Dim fpath As String
Dim fso2 As Object
Dim streamin As Object
Dim i As Integer
Dim r(122) As String
'Create a dialog box to select a file to open
Set fopen = Application.FileDialog(msoFileDialogFilePicker)
'Configure the dialog box to select a single text file only
fopen.AllowMultiSelect = False
fopen.Title = "Select file to open"
fopen.Filters.Clear
fopen.Filters.Add "Text Files", "*.txt"
'Open the dialog box and save the file path selected by the user
If fopen.Show = -1 Then
fpath = fopen.SelectedItems(1)
Else
Exit Sub
End If
'Create a new file system object
Set fso2 = CreateObject("Scripting.FileSystemObject")
'Open the user selected file for reading
Set streamin = fso2.openTextFile(fpath)
'Read data from each line of the file into the corresponding position in the array
i = 0
Do Until i = 123
r(i) = streamin.ReadLine
i = i + 1
Loop
'Close the text file when reading is finished
streamin.Close
'Job data entries into data array
Sheet1.Range("E7").Value = r(0)
Sheet1.Range("E8").Value = r(1)
Sheet1.Range("E12").Value = r(2)
Sheet1.Range("E13").Value = r(3)
Sheet1.Range("E14").Value = r(4)
Sheet1.Range("E18").Value = r(5)
Sheet1.Range("E19").Value = r(6)
Sheet1.Range("E20").Value = r(7)
Sheet1.Range("E21").Value = r(8)
Sheet6.Range("K7").Value = r(9)
Sheet6.Range("M7").Value = r(10)
Sheet5.Range("B6").Value = r(11)
Sheet5.Range("D6").Value = r(12)
Sheet5.Range("G6").Value = r(13)
Sheet5.Range("I6").Value = r(14)
Sheet5.Range("B14").Value = r(15)
Sheet5.Range("D14").Value = r(16)
Sheet5.Range("G14").Value = r(17)
Sheet5.Range("I14").Value = r(18)
Sheet5.Range("B23").Value = r(19)
Sheet5.Range("D23").Value = r(20)
Sheet5.Range("G23").Value = r(21)
Sheet5.Range("I23").Value = r(22)
Sheet5.Range("B31").Value = r(23)
Sheet5.Range("D31").Value = r(24)
Sheet5.Range("G31").Value = r(25)
Sheet5.Range("I31").Value = r(26)
Sheet4.Range("B6").Value = r(27)
Sheet4.Range("D6").Value = r(28)
Sheet4.Range("G6").Value = r(29)
Sheet4.Range("I6").Value = r(30)
Sheet4.Range("B14").Value = r(31)
Sheet4.Range("D14").Value = r(32)
Sheet4.Range("G14").Value = r(33)
Sheet4.Range("I14").Value = r(34)
Sheet4.Range("B36").Value = r(35)
Sheet4.Range("D36").Value = r(36)
Sheet4.Range("G36").Value = r(37)
Sheet4.Range("I36").Value = r(38)
Sheet4.Range("B44").Value = r(39)
Sheet4.Range("D44").Value = r(40)
Sheet4.Range("G44").Value = r(41)
Sheet4.Range("I44").Value = r(42)
Sheet21.Range("A7").Value = r(43)
Sheet21.Range("C7").Value = r(44)
Sheet21.Range("P7").Value = r(45)
Sheet21.Range("R7").Value = r(46)
Sheet21.Range("G9").Value = r(47)
Sheet21.Range("I9").Value = r(48)
Sheet21.Range("N9").Value = r(49)
Sheet21.Range("P9").Value = r(50)
Sheet21.Range("A19").Value = r(51)
Sheet21.Range("C19").Value = r(52)
Sheet21.Range("P19").Value = r(53)
Sheet21.Range("R19").Value = r(54)
Sheet21.Range("A25").Value = r(55)
Sheet21.Range("C25").Value = r(56)
Sheet21.Range("P25").Value = r(57)
Sheet21.Range("R25").Value = r(58)
Sheet21.Range("G27").Value = r(59)
Sheet21.Range("I27").Value = r(60)
Sheet21.Range("N27").Value = r(61)
Sheet21.Range("P27").Value = r(62)
Sheet21.Range("A37").Value = r(63)
Sheet21.Range("C37").Value = r(64)
Sheet21.Range("P37").Value = r(65)
Sheet21.Range("R37").Value = r(66)
Sheet3.Range("D7").Value = r(67)
Sheet3.Range("F7").Value = r(68)
Sheet3.Range("O7").Value = r(69)
Sheet3.Range("Q7").Value = r(70)
Sheet3.Range("B9").Value = r(71)
Sheet3.Range("D9").Value = r(72)
Sheet3.Range("I9").Value = r(73)
Sheet3.Range("K9").Value = r(74)
Sheet3.Range("D19").Value = r(75)
Sheet3.Range("F19").Value = r(76)
Sheet3.Range("O19").Value = r(77)
Sheet3.Range("Q19").Value = r(78)
Sheet3.Range("D25").Value = r(79)
Sheet3.Range("F25").Value = r(80)
Sheet3.Range("O25").Value = r(81)
Sheet3.Range("Q25").Value = r(82)
Sheet3.Range("B27").Value = r(83)
Sheet3.Range("D27").Value = r(84)
Sheet3.Range("I27").Value = r(85)
Sheet3.Range("K27").Value = r(86)
Sheet3.Range("B37").Value = r(87)
Sheet3.Range("D37").Value = r(88)
Sheet3.Range("O37").Value = r(89)
Sheet3.Range("Q37").Value = r(90)
Sheet31.Range("A7").Value = r(91)
Sheet31.Range("C7").Value = r(92)
Sheet31.Range("Q7").Value = r(93)
Sheet31.Range("S7").Value = r(94)
Sheet31.Range("C9").Value = r(95)
Sheet31.Range("E9").Value = r(96)
Sheet31.Range("H9").Value = r(97)
Sheet31.Range("J9").Value = r(98)
Sheet31.Range("A19").Value = r(99)
Sheet31.Range("C19").Value = r(100)
Sheet31.Range("Q19").Value = r(101)
Sheet31.Range("S19").Value = r(102)
Sheet31.Range("A25").Value = r(103)
Sheet31.Range("C25").Value = r(104)
Sheet31.Range("Q25").Value = r(105)
Sheet31.Range("S25").Value = r(106)
Sheet31.Range("C27").Value = r(107)
Sheet31.Range("E27").Value = r(108)
Sheet31.Range("H27").Value = r(109)
Sheet31.Range("J27").Value = r(110)
Sheet31.Range("A37").Value = r(111)
Sheet31.Range("C37").Value = r(112)
Sheet31.Range("Q37").Value = r(113)
Sheet31.Range("S37").Value = r(114)
Sheet11.Range("F8").Value = r(115)
Sheet11.Range("J8").Value = r(116)
Sheet11.Range("M8").Value = r(117)
Sheet11.Range("P8").Value = r(118)
Sheet11.Range("F9").Value = r(119)
Sheet11.Range("J9").Value = r(120)
Sheet11.Range("M9").Value = r(121)
Sheet11.Range("P9").Value = r(122)
'Clear contents of the file name field
Sheet2.Range("M5").Value = ""
MsgBox ("Data file loaded.")
End Sub