Here's the code for the form. It enters my checks and atm entries and balances my statement from my bank off the net.
Sub CheckBookForm()
Application.WindowState = xlMinimized
TestUserForm.Show False
End Sub
Private Sub BalanceFrm_TxB_Balance_Click()
Dim Answer As String
Dim Begin_Bal As String
Dim Bot_Chks As String
Dim Bot_Deps As String
Dim BottomRight As String
Dim Chk_Dep As Integer
Dim Csv_Address As String
Dim Csv_Dep As String
Dim Csvdep_Address As String
Dim CsvDep_Value As String
Dim CsvLoop As Integer
Dim Dep As Integer
Dim Dep_Address As String
Dim Dep_Not_There As String
Dim Dep_Value As String
Dim DepMsg As String
Dim Do_Loop As String
Dim Ending_Bal As String
Dim File_Default As String
Dim File_Name As String
Dim Gas As Sheets
Dim GetCsvFile As String
Dim My_Dir As String
Dim NotHere_Address As String
Dim Question As String
Dim RealFileName As String
Dim Row_Location As String
Dim test As String
Dim This_Cell As String
Dim ThisMonth As String
Dim ThisPath As String
Dim CsvFileName As String
Dim ThisYear As String
Dim Top_Chks As String
Dim Top_Deps As String
Dim TopLeft As String
Dim Version As String
Dim Short_File_Name As String
Dim SavedMonth As String
Dim ThisWorkShet As String
Dim OriginalFileName As String
Dim BackUpFileName As String
Dim CloseThisFile As String
Dim CurrentFile As Variant
Dim MonthValue As String
Dim OldYear As String
Dim NextNewSheet As String
Dim TempBalance As String
Dim Compare1 As Variant
Dim Compare2 As Variant
Dim PlsRedAmount As String
Dim PlsRedDescription As String
Dim Changed_Description As String
Dim PlsCsvRedDate As String
Dim CheckGreenDate As String
Dim CheckNumberGreen As String
Dim ThisFileName As String
Dim CSVExt As Variant
Dim XlsmExt As Variant
Dim ThisAnswer
'Dim ThisFileNameExt As Variant
CSVExt = ".csv"
XlsmExt = ".xlsm"
'************************************************************
ThisFileName = "ChkBook"
'*************************************
'Application.Caption = "Balancing Adam's Checking Account"
'Show needed Sheets
Sheets("header").Select
For Each HiddenSheet In ActiveWorkbook.Sheets
HiddenSheet.Visible = True
Next HiddenSheet
ActiveWindow.SelectedSheets.Visible = False
Sheets("header").Visible = True
Sheets("checking").Select
'Take out unneeded sheets
Sheets("budget").Visible = False
Sheets("gas").Visible = False
Sheets("savings").Visible = False
Sheets("deps").Visible = False
Sheets("addresses").Visible = False
Sheets("paydays").Visible = False
'Sheets("test_checkbook").Visible = False
Sheets("home_insurance").Visible = False
'GoTo pwpassed
Application.ScreenUpdating = False
'Hint
'RealFileName = Chr(34) & SavedMonth & ".xlsm" & Chr(34)
'chr(34)= quote marks
'*******************************
'Test = "yes"
'If Test = "yes" Then GoTo start
'*******************************
GoTo pwpassed
'******************************************************
AskForPW:
PW = InputBox("Enter Password", "Balancing Checkboot")
Application.ScreenUpdating = True
If ActiveCell.Value = ("0") Then MsgBox ActiveCell.Value
Sheets("CSV").Select
Sheets("checking").Select
If PW = ("") Then End
If PW = ("1130") Then GoTo pwpassed
End
'******************************************************
pwpassed:
'FormatChkbook
ClearComparisons
Range("b10: L500").Select
MakeBlack
Range("E7").Select
'******************************************************
Sheets("Checking").Select
allsort
TempBalance = "no"
TempBalance = MsgBox("Run a Temporary Balance?", vbYesNoCancel)
If TempBalance = 6 Then
TempBalance = "yes"
GoTo SkipSavedFiles
Else
TempBalance = "no"
GoTo keepgoing
' else vbcancel=2g
End If
keepgoing:
Application.ScreenUpdating = True
'******************************************************
'Save an original to the current month subdirectory, and then save
'back to original location. If the month location is "end"
'then temporarily change it to January, save, take January off the list.
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Sheets("Matrix").Select
Range("e500").Select
e_u
OldYear = ActiveCell.Value
Range("b500").Select
e_u
MonthValue = Selection.Value
If Not MonthValue = "end" Then GoTo SkipChangeYear
Sheets("Matrix").Select
Range("b500").Select
e_u
MonthValue = Selection.Value
If MonthValue = "end" Then
moved 1
ActiveCell.Value = "January"
Range("e500").Select
e_u
Selection.Clear
End If
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Sheets("Matrix").Select
'Set up the year
Range("e500").Select
e_u
ThisYear = ActiveCell.Value
ThisPath = "j:\data\checkbook\" & ThisFileName & "\" & ThisYear
MkDir ThisPath
'set up the month
Sheets("Matrix").Select
Range("b500").Select
e_u
ThisMonth = ActiveCell.Value
'set up the backup file location and name
BackUpFileName = "j:\data\checkbook\" & ThisFileName & "\" _
& ThisYear & "\" _
& ThisMonth & "-" & _
ThisFileName & "orig.xlsm"
'"ChkBook-orig.xlsm"
'Set up file name to close.
CurrentFile = ThisMonth & "-" & _
ThisFileName & "-orig.xlsm"
'"ChkBook-orig.xlsm"
'*************************************************************
'Save as Backup file name
Sheets("checking").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=BackUpFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Sheets("Matrix").Select
Range("b500").Select
e_u
If MonthValue = "end" Then
Selection.Clear
Range("e500").Select
e_u
moved 1
ActiveCell.Value = OldYear
End If
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
'Save as Backup file name
Sheets("checking").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=BackUpFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Save back as user's original name
'Set up the original file location and name
OriginalFileName = "j:\data\checkbook\" _
& ThisFileName & ".xlsm"
ActiveWorkbook.SaveAs Filename:=OriginalFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Application.DisplayAlerts = True
Sheets("checking").Select
'See if it is the New Year.
Sheets("Matrix").Select
Range("b500").Select
e_u
MonthValue = Selection.Value
'Change the year after December
ChangeYear:
Sheets("Matrix").Select
Range("A2:a13").Select
Selection.Copy
mover 1
ActiveSheet.Paste
Range("e500").Select
e_u
Selection.ClearContents
e_u
ThisYear = ActiveCell.Value
ThisPath = "j:\data\checkbook\" & ThisFileName & "\" & ThisYear
'ChkBook\" & ThisYear
'---------------------------------------------------------------
'---------------------------------------------------------------
'---------------------------------------------------------------
'---------------------------------------------------------------
'---------------------------------------------------------------
'---------------------------------------------------------------
'*******************************************************************
SkipChangeYear:
'Copy Backup file
'*************************************************************
Sheets("Matrix").Select
Range("b500").Select
e_u
ThisMonth = ActiveCell.Value
Range("e500").Select
e_u
ThisYear = ActiveCell.Value
'set up the backup file location and name
BackUpFileName = "j:\data\checkbook" & "\" _
& ThisFileName & "\" _
& ThisYear & "\" _
& ThisMonth & "-" & _
ThisFileName & "-orig.xlsm"
'"ChkBook-orig.xlsm"
'Set up file name to close.
CurrentFile = ThisMonth & "-" & _
ThisFileName & "orig.xlsm"
'"ChkBook-orig.xlsm"
'*************************************************************
'Save as Backup file name
Sheets("checking").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=BackUpFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
thismsgbox:
OriginalFileName = "j:\data\checkbook\" & ThisFileName & ".xlsm"
ActiveWorkbook.SaveAs Filename:=OriginalFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Application.DisplayAlerts = True
Sheets("checking").Select
' GoTo CheckMonthAgain
pls3xthere = "no"
pls10xthere = "no"
Application.ScreenUpdating = False
Sheets("checking").Select
jump "a1"
Sheets("checking").Select
Range("a3000").Select
Answer = Selection.Value
If Not Answer = "y" Then GoTo SkipQuestion
GetCsvFile = InputBox("(Have you made corrections?", "Balancing", Answer)
If GetCsvFile = "n" Then End
GoTo movebalance
SkipSavedFiles:
SkipQuestion:
'GoTo pwpassed
Application.ScreenUpdating = False
'****************************************8
'****************************************8
'Show ALL Sheets, if needed.
'For Each HiddenSheet In ActiveWorkbook.Sheets
' HiddenSheet.Visible = True
'Next HiddenSheet
'ActiveWindow.SelectedSheets.Visible = False
'Sheets("savings").Select
'End
'****************************************8
'Unhide Columns
Columns("B:M").Select
Selection.EntireColumn.Hidden = False
Range("A2").Select
'ESTABLISH AN "END_UP" POINT
Application.ScreenUpdating = False
Sheets("checking").Select
Range("B9").Select
ActiveCell.FormulaR1C1 = "end"
Range("C9").Select
ActiveCell.FormulaR1C1 = "end"
Range("D9").Select
ActiveCell.FormulaR1C1 = "end"
Range("E9").Select
ActiveCell.FormulaR1C1 = "end"
Range("F9").Select
ActiveCell.FormulaR1C1 = "end"
Range("G9").Select
ActiveCell.FormulaR1C1 = "end"
Range("H9").Select
ActiveCell.FormulaR1C1 = "end"
Range("I9").Select
ActiveCell.FormulaR1C1 = "end"
Range("J9").Select
ActiveCell.FormulaR1C1 = "end"
Range("K9").Select
ActiveCell.FormulaR1C1 = "end"
Range("L9").Select
ActiveCell.FormulaR1C1 = "end"
'Clears all old not there stuff
Sheets("CSV").Select
Range("a4
500").Select
Selection.ClearContents
Range("a7").Select
Application.CutCopyMode = False
'Do you want to get the csv file?
Answer = "n"
'response = MsgBox("Get CSV file?", _
vbYesNo)
' If response = vbYes _
' Then
' GoTo GetCSV
' Else
' GoTo movebalance
' End If
' getcsvfile = InputBox("(Get Csv file?", "Balancing", answer)
' If getcsvfile = "n" Then GoTo movebalance
' If getcsvfile = "s" Then GoTo SkipCopy
'Making copies first
GetCSV:
Sheets("not_there").Select
Range("A3:K455").Select
Selection.ClearContents
Range("a2").Select
ActiveCell.FormulaR1C1 = "end"
Range("B2").Select
ActiveCell.FormulaR1C1 = "end"
Range("C2").Select
ActiveCell.FormulaR1C1 = "end"
Range("D2").Select
ActiveCell.FormulaR1C1 = "end"
Range("E2").Select
ActiveCell.FormulaR1C1 = "end"
Range("F2").Select
ActiveCell.FormulaR1C1 = "end"
Application.CutCopyMode = False
'Copy Header Info for starting
Sheets("Checking").Select
Range("b4:g5").Select
Selection.Copy
Sheets("Header").Select
HeaderCSVBalance = ThisMonth & ":" & " Start"
Range("a1").Select
cell_enter HeaderCSVBalance
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Opens and sorts CSV file
Sheets("csv").Select
ChDir "j:\data\checkbook\csv"
File_Default = "j:\data\checkbook\csv\" & _
ThisFileName & ".csv"
'ChkBook.csv ""
File_Default = ThisFileName & ".csv"
'"ChkBook.csv"
Short_File_Name = ThisFileName & ".csv"
'"ChkBook.csv"
'Prompts for filename
File_Name = "j:\data\checkbook\csv\" & _
ThisFileName & ".csv"
'ChkBook.csv ""
'file_name = InputBox("Enter filename)", , file_default, 1, 1)
'new-Excel
Workbooks.OpenText Filename:=(File_Name), Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
'ActiveWorkbook.Close
ZeroDel
Range("A2").Select
ActiveCell.FormulaR1C1 = "end"
Range("B2").Select
ActiveCell.FormulaR1C1 = "end"
Range("C2").Select
ActiveCell.FormulaR1C1 = "end"
Range("D2").Select
ActiveCell.FormulaR1C1 = "end"
Range("E2").Select
ActiveCell.FormulaR1C1 = "end"
Range("F2").Select
ActiveCell.FormulaR1C1 = "end"
Range("G2").Select
ActiveCell.FormulaR1C1 = "DEP"
Range("h2").Select
ActiveCell.FormulaR1C1 = "NMB"
Range("I2").Select
ActiveCell.FormulaR1C1 = "CK"
Range("J2").Select
ActiveCell.FormulaR1C1 = "PLS"
Rows("2:2").RowHeight = 1
Range("A2:K2").Select
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A4").Select
'ChDir "j:\data\checkbook"
'Copy CSV file to CSV sheet
current_csv:
Range("A1").Select
Windows(Short_File_Name).Activate
Range("A1:G500").Select
Selection.Copy
Windows(ThisFileName + XlsmExt).Activate
'Copying to CSV_Bkup
Sheets("CSV_Bkup").Select
Range("a4").Select
ActiveSheet.Paste
Range("H1").Select
'Copying to CSV sheet
Sheets("CSV").Select
Range("a4").Select
ActiveSheet.Paste
Range("H1").Select
' Range("A4:G500").Select
' Selection.Copy
Sheets("CSV_Bkup").Select
Application.CutCopyMode = False
Sheets("CSV").Select
Application.CutCopyMode = False
'Close CSV File
Windows(ThisFileName + CSVExt).Activate
' Windows("ChkBook.csv").Activate
ActiveWindow.Close SaveChanges:=False
Windows(ThisFileName + XlsmExt).Activate
Range("G1").Select
ChDir "j:\data\checkbook"
'Add ending statements to CSV file
Range("a6").Select
GoTo notthistoend
ActiveCell.FormulaR1C1 = "end"
Range("B6").Select
ActiveCell.FormulaR1C1 = "end"
Range("C6").Select
ActiveCell.FormulaR1C1 = "end"
Range("D6").Select
ActiveCell.FormulaR1C1 = "end"
Range("E6").Select
ActiveCell.FormulaR1C1 = "end"
Range("F6").Select
Selection.ClearContents
notthistoend:
Range("G5").Select
ActiveCell.FormulaR1C1 = "DEP"
Range("h5").Select
ActiveCell.FormulaR1C1 = "CHK NMBER"
Range("I5").Select
ActiveCell.FormulaR1C1 = "CHK AMNT"
Range("J5").Select
ActiveCell.FormulaR1C1 = "CSV"
'Sorting selections
Sheets("CSV").Select
Application.CutCopyMode = False
'estiblishing anchors for selections
'top_chks
Range("a6").Select
Top_Chks = Selection.Address
'Bot_chks
Range("d500").Select
e_u
Bot_Chks = Selection.Address
'Bot_deps
Range("e500").Select
e_u
Bot_Deps = Selection.Address
'Top_deps
Range("e6").Select
Top_Deps = Selection.Address
Sheets("CSV").Select
'Sort CSV Checks
Range(Top_Chks, Bot_Chks).Select
Selection.Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort CSV Deposites
Range(Top_Deps, Bot_Deps).Select
Selection.Sort Key1:=Range("E7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("CSV").Select
'Inserts row to separate checks from Pls
Sheets("CSV").Select
Range("b500").Select
e_u
moved 1
row1 = Selection.Row
row2 = Selection.Row
rownumber = row1 & ":" & row2
Rows(rownumber).Select
Selection.Insert Shift:=xlDown
mover 3
ActiveCell.FormulaR1C1 = "stopsearch"
'Establish sorting address for CSV Pls
Sheets("CSV").Select
'Top_PLS
Range("a500").Select
e_u
e_u
top_pls = Selection.Address
'Set sorting key
'Sort_key
mover 3
sort_key = Selection.Address
'Bot_PLS
movel 3
e_d
mover 5
bot_pls = Selection.Address
'Sort CSV PLS
Range(top_pls, bot_pls).Select
Selection.Sort Key1:=Range(sort_key, sort_key), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort Dep's one more time
Range("E6:E370").Select
ActiveWorkbook.Worksheets("CSV").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CSV").Sort.SortFields.Add Key:=Range("E6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("CSV").Sort
.SetRange Range("E6:E500")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G7").Select
'Ok to here.
'DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-
'DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-
'DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-DEPS-
'Get CSV_dep values
compare_deps:
Sheets("CSV").Select
Range("e500").Select
e_u
DepValue = Selection.Value
movel 4
DepDate = Selection.Value
mover 4
CsvDep_Value = Selection.Value
Csvdep_Address = Selection.Address
a = Selection.Value
If a = "end" Then GoTo checkpls
'Get Checkbook Dep Values
Sheets("checking").Select
Range("d500").Select
e_u
moved 1
movel 1
Dep_Value = Selection.Value
Dep_Address = Selection.Address
b = Selection.Value
check_dep_Again:
'If A-csv (deposite)doesn't match B-checking then goto check_all_deps_loop
'Otherwise continue and Input A-csv into the adjacent cell
If Not a = b Then GoTo check_all_deps_loop
mover 1
cell_enter a
Sheets("csv").Select
Range("e500").Select
e_u
Selection.Cut
mover 2
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("checking").Select
Range("B10:D500").Select
Selection.Sort Key1:=Range("D10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B7").Select
'Sorted, now try again
GoTo compare_deps
check_all_deps_loop:
'Keep moving down 1 and looping back to check_dep_again, above,
'until you find a match or get to the end of the list.
'If it isn't in the list then goto CSV_not_there
moved 1
b = Selection.Value
If ActiveCell.Value = ("") Then GoTo csv_not_there
GoTo check_dep_Again
csv_not_there:
starthere:
Application.ScreenUpdating = True
Sheets("checking").Select
jump "b500"
'Range("b500").Select
e_u
moved 1
Selection.Value = DepDate
MakeRed
mover 1
Selection.Value = DepValue
MakeRed
mover 1
Selection.Value = DepValue
MakeRed
movel 1
'The CSV isn't in the checkbook so
'we will enter it into the not_there sheet
Sheets("not_there").Select
Range("a500").Select
e_u
moved 1
cell_enter a
'Then move the Deposite over to the checked column
'and then change it to red.
Sheets("csv").Select
Range("e500").Select
e_u
Selection.Cut
mover 2
ActiveSheet.Paste
Application.CutCopyMode = False
MakeRed
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
movel 4
MakeRed
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
mover 4
e_u
'Loop back up to the top of this routine
GoTo compare_deps
'-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS
'-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS
'-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS
'-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS-PLS
'Get PLS_CSV Values
checkpls:
'csv-pls
Sheets("CSV").Select
Range("d500").Select
e_u
csvpls_value = Selection.Value
csvpls_address = Selection.Address
a = Selection.Value
If a = "stopsearch" Then GoTo checkchk
'Get pls Values
Sheets("checking").Select
Range("L500").Select
e_u
moved 1
movel 1
pls_value = Selection.Value
pls_address = Selection.Address
b = Selection.Value
check_pls_Again:
'If A-csv (Pulse)doesn't match B-checking then goto check_all_pls_loop
'Otherwise continue and Input A-csv into the adjacent cell
If Not a = b Then GoTo check_all_pls_loop
Values_Equal:
mover 1
cell_enter a
'pls-csv
'puthere
Sheets("csv").Select
Range("d500").Select
e_u
'Range(csvpls_address).Select
Selection.Cut
mover 6
ActiveSheet.Paste
plslocation = Selection.Address
Application.CutCopyMode = False
Sheets("checking").Select
Range("J10:M500").Select
ActiveWindow.ScrollRow = 24
Selection.Sort Key1:=Range("L10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I10").Select
GoTo checkpls
check_all_pls_loop:
'Keep moving down 1 and looping back to check_pls_again, above,
'until you find a match or get to the end of the list.
'If it isn't in the list then goto CSV_not_there
moved 1
b = Selection.Value
If ActiveCell.Value = ("") Then GoTo plscsv_not_there
GoTo check_pls_Again
plscsv_not_there:
'The CSV isn't in the checkbook so
'we will enter it into the not_there sheet
Sheets("not_there").Select
Range("f500").Select
e_u
moved 1
cell_enter a
'Then move the Pulse over to the checked column
'and then change it to red.
Sheets("csv").Select
Range("d500").Select
e_u
movel 3
PlsCsvRedDate = Selection.Value
mover 3
Selection.Cut
mover 6
ActiveSheet.Paste
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
MakeRed
new3:
PlsRedAmount = Selection.Value
'Selection.Font.ColorIndex = 3
MakeRed
new1:
movel 7
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
MakeRed
new2:
PlsRedDescription = Selection.Value
'Selection.Font.ColorIndex = 3
GoTo checkpls
'chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-
'chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-
'chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-
'chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-chk-
checkchk:
'Clear out Voids
Sheets("checking").Select
Range("g500").Select
e_u
checkagain:
If Selection.Value = "0" Then GoTo CopyIt
If Selection.Value = "end" Then GoTo StopCheckingVoids
moveu 1
GoTo checkagain
CopyIt:
Selection.Copy
mover 1
ActiveSheet.Paste
movel 1
moveu 1
GoTo checkagain
'Get CSV Values for checks
StopCheckingVoids:
Checking_Checks:
Sheets("checking").Select
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Take off stopsearch, end up then select A value for CSV
Sheets("CSV").Select
Range("d500").Select
e_u
'at csvchk num
csvchknum_value = Selection.Value
csvchknum_address = Selection.Address
n = Selection.Value
If Selection.Value = ("stopsearch") Then Selection.ClearContents
Range("d500").Select
e_u
csvchk_value = Selection.Value
csvchk_address = Selection.Address
a = Selection.Value
movel 2
CSVChk_number = Selection.Value
'If a = "end" Then GoTo Check_checks_for_csv
If a = "end" Then GoTo Start_Comparison
'From here on it's normal like the deposites and the Pulses.
'Sort checking, get selection value B for checkbook and get the value of the check number
ChkValues:
Sheets("checking").Select
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Get chk Values
Sheets("checking").Select
Range("h500").Select
e_u
moved 1
movel 1
chk_value = Selection.Value
chk_address = Selection.Address
b = Selection.Value
movel 2
chknumber = Selection.Value
mover 2
check_chk_Again:
'If A-csv (Checks)doesn't match B-checking then goto check_all_chks_loop
'Otherwise continue and Input A-csv into the adjacent cell
Sheets("checking").Select
If Not a = b Then GoTo check_all_chks_loop
mover 1
cell_enter a
'Move matching csv to the column on the right, aloge with check number,
'then sort checking again
Sheets("csv").Select
Range("d500").Select
e_u
'Range(csvchk_address).Select
Selection.Cut
mover 5
ActiveSheet.Paste
Application.CutCopyMode = False
movel 1
cell_enter CSVChk_number
Application.CutCopyMode = False
Sheets("checking").Select
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D10").Select
GoTo Checking_Checks
check_all_chks_loop:
'=======at checking-52=========
moved 1
b = Selection.Value
If ActiveCell.Value = ("") Then GoTo chk_csv_not_there
GoTo check_chk_Again
chk_csv_not_there:
'chk_csv_not_there
Sheets("not_there").Select
Range("c500").Select
e_u
moved 1
cell_enter a
movel 1
cell_enter CSVChk_number
Sheets("csv").Select
Range(csvchk_address).Select
Selection.Cut
mover 5
ActiveSheet.Paste
Application.CutCopyMode = False
movel 7
Selection.Cut
mover 6
ActiveSheet.Paste
Application.CutCopyMode = False
e_u
GoTo checkchk
'Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS
'Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS
'Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS
'Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS
'Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS Copy/Move PLS
'
'Csv not in checkbook pulse list, checking the check list.
'If A=B then put a zero and add the amount to the description
'and then move it to both columns in the pulse list.
'*************************************************
'See if A is in checks
'If A is in checks:
'If A is a duplicate in checks
'Ignore and keep searching
'If A is not a duplicate
'Move (NOT copy) to "In Checkbook" Column
'Append A to description
'Copy A to PLs A&B
'Put todays date on PLS
'If A is not in Checks AND no duplicates
'Move to "Not in Checkbook" Column
'Start over until A=end
'A= Not_there bottom of list
'B= comparison
'C= Ajacent Comparison
'D= Description
'Temp Location = F
'Pls Here = Column E
'PLS not in ChkBk = D
'If A is in checks:
Start_Comparison:
Sheets("not_there").Select
Range("f500").Select
e_u
' ("Not_there: Bottom Pls Temp")
a = Selection.Value
notthere_address = Selection.Address
If Selection.Value = "end" Then GoTo Pls_Question
Sheets("checking").Select
Range("g500").Select
e_u
' ("Checking: Checks #1")
GoTo aroundthis
check_check_again:
' ("Checking: moving up one at a time")
moveu 1
aroundthis:
' ("Checking: Checks #1")
Sheets("checking").Select
b = Selection.Value
If b = "end" Then GoTo not_in_chkbk
If Not a = b Then GoTo check_check_again
'Is A an inputted check?
Sheets("checking").Select
mover 1
b = Selection.Value
movel 1
If b = "end" Then GoTo Enter_PLS
If Not a = b Then GoTo Enter_PLS
GoTo check_check_again
Enter_PLS:
'MsgBox "Changing to green and adding the amount to the description."
Sheets("checking").Select
movel 1
CheckGreenDate = Selection.Value
'mg
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
movel 1
CheckNumberGreen = Selection.Value
'mg
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
mover 2
cell_enter 0
mover 1
cell_enter 0
mover 1
'Checking: Check Descr.
c = Selection.Value
Changed_Description = c & ":" & a
cell_enter Changed_Description
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
'Selection.Font.ColorIndex = 5
Changed_Description = Selection.Value
'boxmessage = "3 - " & Changed_Description & "C= " & c & ": " & "D= " & d & ": " & "E= " & e & ": " & "F= " & f
Sheets("checking").Select
'Copy value to end of Pls in chking
Sheets("checking").Select
Range("j500").Select
e_u
moved 1
'Checking: bottom empty Pls Date
cell_enter CheckGreenDate
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
'Selection.Font.ColorIndex = 5
mover 1
'Checking: Empty - Pls #1
cell_enter a
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
'Selection.Font.ColorIndex = 5
mover 1
'Checking: Empty - Pls #2
cell_enter a
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
'Selection.Font.ColorIndex = 5
pls10xthere = "yes"
mover 1
Changed_description_PlusChkNumber = CheckNumberGreen & ":" & Changed_Description
cell_enter Changed_description_PlusChkNumber
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
movel 1
'Delete value in notthere sheet
'Start over until A=end
'A= Not_there bottom of list
'B= comparison
'C= Ajacent Comparison
'D= Description
'Temp Location = F
'Pls Here = Column E
'PLS not in ChkBk = D
'If PLS is in checkbook and was moved (matches=yes):
Sheets("Not_there").Select
Range("f500").Select
e_u
'Not_there: Bottom - Pls_Temp
Selection.ClearContents
Range("e500").Select
e_u
moved 1
cell_enter a
Sheets("csv").Select
Range("j7").Select
e_d
matches = no
GoTo Start_Comparison
not_in_chkbk:
Sheets("checking").Select
Range("j500").Select
e_u
moved 1
'Checking: bottom empty Pls Date
cell_enter PlsCsvRedDate
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
mover 1
'Checking: Empty - Pls #1
cell_enter a
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
mover 1
'Checking: Empty - Pls #2
cell_enter a
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
NotInChkbk = "yes"
mover 1
cell_enter PlsRedDescription
Sheets("Not_there").Select
Range("f500").Select
e_u
'Not_there: Bottom - Pls_Temp
Selection.ClearContents
Sheets("Not_there").Select
Range("d500").Select
e_u
moved 1
cell_enter a
Sheets("csv").Select
Range("j7").Select
e_d
GoTo Start_Comparison
'*************************************************
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
'movebalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance MoveBalance
Pls_Question:
'Sort Deposites
Sheets("checking").Select
Application.CutCopyMode = False
Sheets("checking").Select
Range("B10:D500").Select
Selection.Sort Key1:=Range("D10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort Checks
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort pulse
Range("J10:M500").Select
ActiveWindow.ScrollRow = 24
Selection.Sort Key1:=Range("L10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I10").Select
Application.ScreenUpdating = True
Sheets("checking").Select
jump "a1"
Pulse_done "n"
Range("A1").Select
Sheets("not_there").Select
Range("a3").Select
If Selection.Value > 0.001 Then
MsgBox "Check your Checks"
Pulse_done "y"
End If
Range("c3").Select
If Selection.Value > 0.001 Then
DepMsg = Selection.Value
MsgBox "Check your Deposites"
MsgBox "This Deposite is not in the checkbook" & ": " & DepMsg
Pulse_done "y"
End If
Range("a3").Select
If Not Selection.Value > 0.001 Then GoTo movebalance
'This is a ligit end statement, do not delete
'reming out End
movebalance:
'Sort Deposites
Sheets("checking").Select
Application.CutCopyMode = False
'Sort Deposites
Sheets("checking").Select
Range("B10:D500").Select
Selection.Sort Key1:=Range("D10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort Checks
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort pulse
Range("J10:M500").Select
ActiveWindow.ScrollRow = 24
Selection.Sort Key1:=Range("L10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I10").Select
'FormatMonth
'Copy Checkbook to Backup Sheet
Sheets("Checking").Select
Range("A1:R503").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("backup").Select
Range("a1").Select
ActiveSheet.Paste
Range("B1").Select
'Input
'Choose which month to copy to
'CheckMonthAgain
CheckMonthAgain:
If TempBalance = "yes" Then GoTo endnow
Sheets("Matrix").Select
Range("b500").Select
e_u
MonthValue = Selection.Value
Selection.ClearContents
Sheets("checking").Select
ThisMonth = InputBox("What Month?", "Balancing Checkbook", MonthValue)
If ThisMonth = "stop" Then GoTo endbalance
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
NextNewSheet = ThisMonth & "_" & ThisYear
ThisMonth = NextNewSheet
sthere:
Sheets("Matrix").Select
Set NewSheet = Worksheets.Add
NewSheet.Name = NextNewSheet
'starthere
'Copy Checking Sheet into CurrentMonth
Sheets("checking").Select
Range("A1:R513").Select
Selection.Copy
Sheets("currentmonth").Select
Range("A1").Select
ActiveSheet.Paste
'Copy Checking Sheet into thismonth
Sheets(ThisMonth).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("currentmonth").Select
Application.CutCopyMode = False
'Make a copy of this to the current year subdir and current month.xls
Sheets("Matrix").Select
Range("e500").Select
e_u
ThisYear = ActiveCell.Value
ThisPath = "j:\data\checkbook\" & _
ThisFileName & "\" & ThisYear & "\"
'ChkBook\" & ThisYear & "\"
SavedMonth = ThisPath & ThisMonth & "-" & ThisFileName
RealFileName = SavedMonth & ".xlsm"
'MsgBox "Realfilename is: " & RealFileName
ThisWorkShet = ThisMonth & "-" & ThisFileName & ".xlsm"
'Save the file as the current year and month
ActiveWorkbook.SaveAs Filename:=RealFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Range("A52").Select
'Copy Header for Changed registry
Sheets("checking").Select
Range("b4:g5").Select
Selection.Copy
Sheets("Header").Select
HeaderCSVBalance = ThisMonth & ":" & " Changed"
Range("a5").Select
cell_enter HeaderCSVBalance
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'MsgBox thismonth
Sheets(ThisMonth).Select
Application.CutCopyMode = False
'st;
Sheets("Matrix").Select
Range("e500").Select
e_u
ThisYear = ActiveCell.Value
ThisPath = "j:\data\checkbook\" & _
ThisFileName & "\" & ThisYear & "\"
'chkBook\" & ThisYear & "\"
SavedMonth = ThisPath & ThisMonth & ThisFileName
CsvFileName = SavedMonth & ".csv"
notestcsv:
'Open the CSV file and save it under the month's subdir
'ChDir "J:\Data\Checkbook\csv"
nodomsg:
'Open csv file.
Workbooks.Open Filename:="J:\Data\Checkbook\csv\" & ThisFileName & ".csv"
'Save as user in year\month
ActiveWorkbook.SaveAs Filename:=CsvFileName _
, _
FileFormat:=xlCSV, CreateBackup:=False
'Close CSV File
ActiveWindow.Close SaveChanges:=False
' Windows(ThisFileName + xlsmext).Activate
Windows(ThisWorkShet).Activate
pastsave:
ShiftDep:
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
'Now we will move the data to the right sheet and sort everything
'1-Deleting balanced deposites in Checkbook sheet
Sheets("checking").Select
Compare1 = Range("c7").Value
If Compare1 <= 0 Then MsgBox Compare1
If Compare1 <= 0 Then GoTo SkipMovingDeposites
'code works
Sheets("checking").Select
Range("d500").Select
e_u
BottomRight = Selection.Address
e_u
moved 1
movel 2
TopLeft = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
'2-Move up unbalanced Deposites in Checkbook sheet
Sheets("checking").Select
Compare1 = Range("c7").Value
'Compare1 = Selection.Value
Compare2 = Range("d7").Value
'Compare2 = Selection.Value
If Compare1 = Compare2 Then GoTo SkipMovingDeposites
Range("d500").Select
e_u
moved 1
movel 1
moved 1
If ActiveCell.Value = ("") Then GoTo NOTMOVEUPDEP
'..........................
Sheets("checking").Select
Range("d500").Select
e_u
moved 1
movel 2
e_d
TopLeft = Selection.Address
e_d
mover 1
BottomRight = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.Cut
Range("d500").Select
e_u
moved 1
movel 2
ActiveSheet.Paste
Application.CutCopyMode = False
NOTMOVEUPDEP:
FormatChkbook
'3-Delete unbalanced deposites in ThisMonth sheet
'code works
Sheets(ThisMonth).Select
Range("d500").Select
e_u
moved 1
movel 2
TopLeft = Selection.Address
e_d
mover 1
BottomRight = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
FormatMonth
SkipMovingDeposites:
'1-Deleting balanced checks in Checking sheet
Sheets("checking").Select
Compare1 = Range("g7").Value
If Compare1 <= 0 Then GoTo SkipMovingChecks
'code works
Sheets("checking").Select
Range("h500").Select
e_u
mover 1
BottomRight = Selection.Address
Range("h500").Select
e_u
e_u
moved 1
movel 3
TopLeft = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
'2-Moving up unbalanced checks in Checking sheet
Sheets("checking").Select
Compare1 = Range("g7").Value
'compare1 = Selection.Value
Compare2 = Range("h7").Value
'compare2 = Selection.Value
If Compare1 = Compare2 Then GoTo SkipMovingChecks
Range("H500").Select
e_u
moved 1
movel 1
moved 1
If ActiveCell.Value = ("") Then GoTo NOTMOVEUPCHECKS
'..........................
Range("i500").Select
e_u
BottomRight = Selection.Address
movel 4
e_u
TopLeft = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.Cut
Range("h500").Select
e_u
moved 1
movel 3
ActiveSheet.Paste
Application.CutCopyMode = False
NOTMOVEUPCHECKS:
FormatChkbook
'3-Delete unbalanced Checks in ThisMonth sheet
Sheets(ThisMonth).Select
Range("I500").Select
e_u
BottomRight = Selection.Address
Range("h500").Select
e_u
moved 1
movel 3
TopLeft = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
FormatMonth
SkipMovingChecks:
'1-Deleting balanced PLS in Checkbook sheet
Sheets("checking").Select
Compare1 = Range("k7").Value
If Compare1 <= 0 Then GoTo SkipMovingPulse
'code works
Sheets("checking").Select
Range("L500").Select
e_u
mover 1
BottomRight = Selection.Address
movel 1
e_u
moved 1
movel 2
TopLeft = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
'2-Move up unbalanced PLS in Checkbook sheet
Sheets("checking").Select
Compare1 = Range("k7").Value
'compare1 = Selection.Value
Compare2 = Range("L7").Value
'compare2 = Selection.Value
If Compare1 = Compare2 Then GoTo SkipMovingPulse
Range("L500").Select
e_u
moved 1
movel 1
If ActiveCell.Value = ("") Then GoTo NOTMOVEUPPLS
moved 1
If ActiveCell.Value = ("") Then GoTo NOTMOVEUPPLS
'..........................
Sheets("checking").Select
Range("L500").Select
e_u
moved 1
movel 2
e_d
TopLeft = Selection.Address
e_d
mover 3
BottomRight = Selection.Address
movel 2
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.Cut
Range("L500").Select
e_u
moved 1
movel 2
ActiveSheet.Paste
Application.CutCopyMode = False
NOTMOVEUPPLS:
FormatChkbook
'3-Delete unbalanced PLS in ThisMonth sheet
'code works
Sheets(ThisMonth).Select
'Sort pulse
Range("J10:M500").Select
ActiveWindow.ScrollRow = 24
Selection.Sort Key1:=Range("L10"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I10").Select
'changed
Range("L500").Select
e_u
moved 1
movel 2
TopLeft = Selection.Address
e_d
mover 3
BottomRight = Selection.Address
selectionarea = TopLeft & ":" & BottomRight
Range(selectionarea).Select
Selection.ClearContents
Application.CutCopyMode = False
FormatMonth
Application.ScreenUpdating = False
SkipMovingPulse:
'**************************************
'**************************************
'**************************************
'Copy CSV File into Months sheet (M1)
Sheets("csv_bkup").Select
Range("a1:k500").Select
Selection.Copy
Sheets(ThisMonth).Select
Range("n1").Select
ActiveSheet.Paste
'**************************************
'**************************************
'**************************************
'**************************************
'start
'Copy Header for Balanced Sheet
Sheets(ThisMonth).Select
Range("b4:g5").Select
Selection.Copy
Sheets("Header").Select
HeaderCSVBalance = ThisMonth & ":" & " Balanced"
Range("a9").Select
cell_enter HeaderCSVBalance
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Copy Balance Forward
Sheets("header").Select
Range("e11").Select
Selection.Copy
Sheets("Checking").Select
Range("B5").Select
ActiveSheet.Paste
'Copy Header for After Sheet
Range("b4:g5").Select
Selection.Copy
Sheets("Header").Select
HeaderCSVBalance = ThisMonth & ":" & " After"
Range("a13").Select
cell_enter HeaderCSVBalance
Range("A14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
GoTo SkipThis
Sheets("header").Select
Range("e1:e7").Select
Selection.Copy
Sheets(ThisMonth).Select
Range("a1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("header").Select
Application.CutCopyMode = False
SkipThis:
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
endbalance:
Sheets("checking").Select
Range("E10:I500").Select
Application.CutCopyMode = False
Sheets("Header").Select
Range("E7").Select
Selection.Copy
Range("b2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("a2").Select
Application.CutCopyMode = False
hide_columns:
Sheets("checking").Select
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Columns("L:L").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
'Clean up and reset macro
Sheets("checking").Select
allsort
Application.CutCopyMode = False
Sheets("currentmonth").Select
allsort
Application.CutCopyMode = False
Sheets(ThisMonth).Select
allsort
Application.CutCopyMode = False
ThisMonth = "start"
Pulse_done "no"
Application.ScreenUpdating = True
Sheets("Header").Select
Range("E3,E7,E11,e15").Select
Range("E15").Activate
Selection.NumberFormat = "#,##0.00"
Range("D12").Select
endnow:
AutoFitChecks
'Sort Deposites
Sheets("checking").Select
Application.CutCopyMode = False
'Sort Deposites
Sheets("checking").Select
Range("B10:D500").Select
Selection.Sort Key1:=Range("D10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort Checks
Range("E10:I500").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sort pulse
Range("J10:M500").Select
ActiveWindow.ScrollRow = 24
Selection.Sort Key1:=Range("L10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("I10").Select
'MsgBox "Clearing entries"
'If TempBalance = "yes" _
' Then
'ClearComparisons
' Else
' GoTo EndBal
'End If
GoTo EndBal
'Save as original user's Name and close
OriginalFileName = "j:\data\checkbook\" & ThisFileName & ".xlsm"
ActiveWorkbook.SaveAs Filename:=OriginalFileName _
, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
EndBal:
Application.ScreenUpdating = True
AutoFitChecks
SkipToHere:
Sheets("header").Select
'Fill out forms to show status of balance
'Ending Balance Form:
EndingFrm_TxB_Beginning.Value = Range("a15").Value
EndingFrm_TxB_Deposites.Value = Range("b15").Value
EndingFrm_TxB_Checks.Value = Range("c15").Value
EndingFrm_TxB_Pulse.Value = Range("d15").Value
EndingFrm_TxB_EndingBalance.Value = Range("e15").Value
'Change from statement Form:
ChangedFrm_TxB_Beginning.Value = Range("a7").Value
ChangedFrm_TxB_Deps.Value = Range("b7").Value
ChangedFrm_TxB_Checks.Value = Range("c7").Value
ChangedFrm_TxB_Pulse.Value = Range("d7").Value
ChangedFrm_TxB_EndingBalance.Value = Range("e7").Value
'Beginning Balance Form:
BeginningBalanceFrm_TxB_Beginning.Value = Range("a3").Value
BeginningBalanceFrm_TxB_Deps.Value = Range("b3").Value
BeginningBalanceFrm_TxB_Chks.Value = Range("c3").Value
BeginningBalanceFrm_TxB_Pulse.Value = Range("d3").Value
BeginningBalanceFrm_TxB_EndingBal.Value = Range("e3").Value
'Statement Form:
StatementFrm_TxB_Beginning.Value = Range("a11").Value
StatementFrm_TxB_Deps.Value = Range("b11").Value
StatementFrm_TxB_Checks.Value = Range("c11").Value
StatementFrm_TxB_Pulse.Value = Range("d11").Value
StatementFrm_TxB_EndingBalance.Value = Range("e11").Value
'Statement Difference
StatementDifference_Txb.Value = x
begin = BeginningBalanceFrm_TxB_EndingBal.Value
ending = EndingFrm_TxB_EndingBalance.Value
StatementDifference_Txb.Value = ending - begin
AutoFitChecks
allsort
'ActiveWorkbook.Save
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
End Sub
Private Sub BalanceFrm_TxB_Close_Click()
'MsgBox ("MAKE SURE YOU WANT TO SAVE!! But, don't save from here! Go to the Checking tab!")
MultiPage1_Change
AutoFitChecks
Application.WindowState = xlMaximized
Unload Me
CmB_Cancel_Click
End Sub
Private Sub Budget_15_Cmb_Cancel_Click()
AutoFitChecks
Range("A1").Select
'Save
'AllUp
Application.WindowState = xlMaximized
Unload Me
answerthis = MsgBox("Do you want to close AND SAVE the Checkbook?", vbYesNo)
If answerthis = 6 Then
ThisWorkbook.Save
Application.DisplayAlerts = False
Application.Quit
End If
Application.WindowState = xlMaximized
Unload Me
End Sub
Private Sub Budget_Cmb_Cancel_Click()
AutoFitChecks
Range("A1").Select
'Save
'AllUp
Application.WindowState = xlMaximized
Unload Me
answerthis = MsgBox("Do you want to close AND SAVE the Checkbook?", vbYesNo)
If answerthis = 6 Then
ThisWorkbook.Save
Application.DisplayAlerts = False
Application.Quit
End If
Application.WindowState = xlMaximized
Unload Me
End Sub
Private Sub Budget_Cmb_Enter_Click()
Sheets("budget").Select
'*********************************************************
If Budget_Cbx_Months.Value = "January" Then Range("januaryheader").Select
If Budget_Cbx_Months.Value = "February" Then Range("februaryheader").Select
If Budget_Cbx_Months.Value = "March" Then Range("marchheader").Select
If Budget_Cbx_Months.Value = "April" Then Range("aprilheader").Select
If Budget_Cbx_Months.Value = "May" Then Range("mayheader").Select
If Budget_Cbx_Months.Value = "June" Then Range("juneheader").Select
If Budget_Cbx_Months.Value = "July" Then Range("julyheader").Select
If Budget_Cbx_Months.Value = "August" Then Range("augustheader").Select
If Budget_Cbx_Months.Value = "September" Then Range("septembetheader").Select
If Budget_Cbx_Months.Value = "October" Then Range("octoberheader").Select
If Budget_Cbx_Months.Value = "November" Then Range("novemberheader").Select
If Budget_Cbx_Months.Value = "December" Then Range("decemberheader").Select
'*********************************************
Range("U1:X31").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 4
.TintAndShade = 0.399945066682943
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.349986266670736
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 4
.TintAndShade = 0.399945066682943
.Weight = xlThick
End With
End Sub
'***************************************************
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'3
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
'*******************************************
If Budget_Cbx_Months.Value = "January" Then Range("BA1").Select
If Budget_Cbx_Months.Value = "February" Then Range("I1").Select
If Budget_Cbx_Months.Value = "March" Then Range("m1").Select
If Budget_Cbx_Months.Value = "April" Then Range("q1").Select
If Budget_Cbx_Months.Value = "May" Then Range("U1").Select
If Budget_Cbx_Months.Value = "June" Then Range("Y1").Select
If Budget_Cbx_Months.Value = "July" Then Range("AC1").Select
If Budget_Cbx_Months.Value = "August" Then Range("AG1").Select
If Budget_Cbx_Months.Value = "September" Then Range("AK1").Select
If Budget_Cbx_Months.Value = "October" Then Range("AO1").Select
If Budget_Cbx_Months.Value = "November" Then Range("AS1").Select
If Budget_Cbx_Months.Value = "December" Then Range("AW1").Select
'*******************************************
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
mover 1
'3
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0.399945066682943
.PatternTintAndShade = 0
End With
Range("U5").Select
'test area
'********************************************
AroundAllThis:
If Budget_Cbx_Months.Value = "January" Then GoTo Jan
If Budget_Cbx_Months.Value = "February" Then GoTo February
If Budget_Cbx_Months.Value = "March" Then GoTo March
If Budget_Cbx_Months.Value = "April" Then GoTo April
If Budget_Cbx_Months.Value = "May" Then GoTo May
If Budget_Cbx_Months.Value = "June" Then GoTo June
If Budget_Cbx_Months.Value = "July" Then GoTo July
If Budget_Cbx_Months.Value = "August" Then GoTo Aug
If Budget_Cbx_Months.Value = "September" Then GoTo Sept
If Budget_Cbx_Months.Value = "October" Then GoTo Oct
If Budget_Cbx_Months.Value = "November" Then GoTo Nov
If Budget_Cbx_Months.Value = "December" Then GoTo Dec
Jan:
If Budget_Chbx_15th.Value = True Then GoTo CopJan15
Jan1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("j6:j75").Select
ValueOnly
Application.CutCopyMode = False
'Range("K5").Select
' Selection.Value = "1a"
Range("K5").Select
GoTo endcheckbudget
CopJan15:
Jan15Total = Range("K4").Value
Range("D6:D75").Select
Selection.Copy
Range("k6:k75").Select
ValueOnly
Application.CutCopyMode = False
Range("K5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'Range("A2:a13").Select
' Selection.Copy
' mover 1
' ActiveSheet.Paste
If Budget_Chbx_15th.Value = True Then Budget_Chbx_15th.Value = False
GoTo endcheckbudget
'TxB_DepAmount.Value = "0.00"
'************************************************************************************
'************************************************************************************
'************************************************************************************
February:
Sheets("budget").Select
CopFeb1:
If Budget_Chbx_15th.Value = True Then GoTo CopFeb15
Feb1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("n6:n75").Select
ValueOnly
Application.CutCopyMode = False
'Range("K5").Select
' Selection.Value = "1a"
Range("o5").Select
GoTo endcheckbudget
CopFeb15:
Feb15Total = Range("o4").Value
Range("D6:D75").Select
Selection.Copy
Range("O6:O75").Select
ValueOnly
Application.CutCopyMode = False
Range("O5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'Range("A2:a13").Select
' Selection.Copy
' mover 1
' ActiveSheet.Paste
'TxB_DepAmount.Value = "0.00"
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
March:
Sheets("budget").Select
CopMARCH1:
If Budget_Chbx_15th.Value = True Then GoTo CopMARCH15
MARCH1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("R6:R75").Select
ValueOnly
Application.CutCopyMode = False
Range("S5").Select
GoTo endcheckbudget
CopMARCH15:
MARCH15Total = Range("S4").Value
Range("D6:D75").Select
Selection.Copy
Range("S6:S75").Select
ValueOnly
Application.CutCopyMode = False
Range("S5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
April:
Sheets("budget").Select
CopApril1:
If Budget_Chbx_15th.Value = True Then GoTo CopApril15
April1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("V6:V75").Select
ValueOnly
Application.CutCopyMode = False
Range("W5").Select
GoTo endcheckbudget
CopApril15:
April15Total = Range("W4").Value
Range("D6:D75").Select
Selection.Copy
Range("W6:W75").Select
ValueOnly
Application.CutCopyMode = False
Range("W5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
May:
Sheets("budget").Select
Copmay1:
If Budget_Chbx_15th.Value = True Then GoTo Copmay15
may1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("z6:z75").Select
ValueOnly
Application.CutCopyMode = False
Range("aa5").Select
GoTo endcheckbudget
Copmay15:
may15Total = Range("aa4").Value
Range("D6:D75").Select
Selection.Copy
Range("aa6:aa75").Select
ValueOnly
Application.CutCopyMode = False
Range("aa5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
June:
Sheets("budget").Select
CopJune1:
If Budget_Chbx_15th.Value = True Then GoTo CopJune15
June1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("AD6:AD75").Select
ValueOnly
Application.CutCopyMode = False
Range("AE5").Select
GoTo endcheckbudget
CopJune15:
June15Total = Range("aE4").Value
Range("D6:D75").Select
Selection.Copy
Range("aE6:aE75").Select
ValueOnly
Application.CutCopyMode = False
Range("aE5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
July:
Sheets("budget").Select
CopJuly1:
If Budget_Chbx_15th.Value = True Then GoTo CopJuly15
July1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("Ah6:Ah75").Select
ValueOnly
Application.CutCopyMode = False
Range("AI5").Select
GoTo endcheckbudget
CopJuly15:
July15Total = Range("AI4").Value
Range("D6:D75").Select
Selection.Copy
Range("aI6:aI75").Select
ValueOnly
Application.CutCopyMode = False
Range("aI5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
Aug:
Sheets("budget").Select
CopAug1:
If Budget_Chbx_15th.Value = True Then GoTo CopAug15
Aug1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("AL6:AL75").Select
ValueOnly
Application.CutCopyMode = False
Range("AM5").Select
GoTo endcheckbudget
CopAug15:
Aug15Total = Range("AM4").Value
Range("D6:D75").Select
Selection.Copy
Range("aM6:aM75").Select
ValueOnly
Application.CutCopyMode = False
Range("aM5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
Sept:
Sheets("budget").Select
CopSept1:
If Budget_Chbx_15th.Value = True Then GoTo CopSept15
Sept1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("AP6:AP75").Select
ValueOnly
Application.CutCopyMode = False
Range("AQ5").Select
GoTo endcheckbudget
CopSept15:
Sept15Total = Range("AQ4").Value
Range("D6:D75").Select
Selection.Copy
Range("aQ6:aQ75").Select
ValueOnly
Application.CutCopyMode = False
Range("aQ5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
Oct:
Sheets("budget").Select
CopOct1:
If Budget_Chbx_15th.Value = True Then GoTo CopOct15
Oct1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("AT6:AT75").Select
ValueOnly
Application.CutCopyMode = False
Range("AU5").Select
GoTo endcheckbudget
CopOct15:
Oct15Total = Range("AU4").Value
Range("D6:D75").Select
Selection.Copy
Range("aU6:aU75").Select
ValueOnly
Application.CutCopyMode = False
Range("aU5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
Nov:
Sheets("budget").Select
CopNov1:
If Budget_Chbx_15th.Value = True Then GoTo CopNov15
Nov1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("AX6:AX75").Select
ValueOnly
Application.CutCopyMode = False
Range("AY5").Select
GoTo endcheckbudget
CopNov15:
Nov15Total = Range("AY4").Value
Range("D6:D75").Select
Selection.Copy
Range("aY6:aY75").Select
ValueOnly
Application.CutCopyMode = False
Range("aY5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
'************************************************************************************
Dec:
Sheets("budget").Select
CopDec1:
If Budget_Chbx_15th.Value = True Then GoTo CopDec15
Dec1Total = Range("j4").Value
Range("c6:c75").Select
Selection.Copy
Range("Bb6:Bb75").Select
ValueOnly
Application.CutCopyMode = False
Range("Bc5").Select
GoTo endcheckbudget
CopDec15:
Dec15Total = Range("BC4").Value
Range("D6:D75").Select
Selection.Copy
Range("Bc6:Bc75").Select
ValueOnly
Application.CutCopyMode = False
Range("Bc5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'************************************************************************************
'************************************************************************************
'************************************************************************************
endcheckbudget:
If Budget_Chbx_15th.Value = True Then Budget_Chbx_15th.Value = False
AutoFitChecks
Sheets("budget").Select
AUTOFIT
End Sub
Private Sub ChkB_Checks_Click()
ChkB_Pulse = False
ChkB_Gas = False
ChkB_Void = False
CheckFrm_Cmbt_Enter.SetFocus
End Sub
Private Sub ChkB_Gas_Click()
ChkB_Checks = False
ChkB_Pulse = False
ChkB_Void = False
CheckFrm_Cmbt_Enter.SetFocus
End Sub
Private Sub ChkB_Pulse_Click()
ChkB_Checks = False
ChkB_Gas = False
ChkB_Void = False
CheckFrm_Cmbt_Enter.SetFocus
End Sub
Private Sub ChkB_Void_Click()
ChkB_Checks = False
ChkB_Pulse = False
ChkB_Gas = False
CheckFrm_Cmbt_Enter.SetFocus
End Sub
Private Sub CmB_Cancel_Click()
AutoFitChecks
Range("A1").Select
'Save
'AllUp
Application.WindowState = xlMaximized
Unload Me
answerthis = MsgBox("Do you want to close AND SAVE the Checkbook?", vbYesNo)
If answerthis = 6 Then
'ThisWorkbook.Save
Save
Application.DisplayAlerts = False
Application.Quit
End If
Application.WindowState = xlMaximized
Unload Me
End Sub
Private Sub CmB_Date_Change()
End Sub
Private Sub CmB_Description_Change()
End Sub
Private Sub Cmb_Reset_Click()
Call UserForm_Initialize
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub CheckFrm_Cmbt_Enter_Click()
ActiveWorkbook.Sheets("checking").Activate
Sheets("checking").Select
hideColumns
'***************************************************
'Make sure only one box is picked.
If ChkB_Checks = False Then
If ChkB_Pulse = False Then
If ChkB_Gas = False Then
If ChkB_Void = False Then GoTo errormsg
End If
End If
End If
'***********-*****
If ChkB_Checks = True Then
If ChkB_Pulse = True Then
GoTo errormsg2
End If
End If
If ChkB_Checks = True Then
If ChkB_Gas = True Then
GoTo errormsg2
End If
End If
If ChkB_Pulse = True Then
If ChkB_Gas = True Then
GoTo errormsg2
End If
End If
If ChkB_Void = True Then
If ChkB_Checks = True Then
GoTo errormsg2
End If
End If
If ChkB_Void = True Then
If ChkB_Pulse = True Then
GoTo errormsg2
End If
End If
If ChkB_Void = True Then
If ChkB_Gas = True Then
GoTo errormsg2
End If
End If
'***************************************************
'****************
'Checks for Void
If ChkB_Void = True Then
Sheets("checking").Select
Range("e9").Select
e_d
thisvalue = Selection.Value
moved 1
checknumber = thisvalue + 1
cell_enter checknumber
mover 1
Selection.Value = CmB_Date.Value
mover 1
Selection.Value = "0"
mover 1
Selection.Value = "0"
mover 1
Selection.Value = "Void"
Range("e9").Select
e_d
thisvalue = Selection.Value
moved 1
checknumber = thisvalue + 1
TxtB_Number.Value = checknumber
GoTo StopActiveButton
End If
'End putting in Void
'****************
If TxTB_Amount.Value = "" Then GoTo StopActiveButton
GoTo NotEmptyValue
'EmptyValue:
'GoTo StopActiveButton
NotEmptyValue:
If ChkB_Pulse = True Then
pulse
End If
If ChkB_Gas = True Then
Gas
GoTo StopActiveButton
End If
'If TxTB_Amount.Value = "" Then UserForm_Initialize
starthere1:
'***************************************************
'Start entering checks
If Not ChkB_Checks = True Then GoTo StopActiveButton
Range("e9").Select
e_d
thisvalue = Selection.Value
moved 1
checknumber = thisvalue + 1
cell_enter checknumber
mover 1
Selection.Value = CmB_Date.Value
mover 1
Selection.Value = TxTB_Amount.Value
mover 2
Selection.Value = CmB_Description.Value
Sheets("checking").Select
Range("e9").Select
e_d
thisvalue = Selection.Value
moved 1
checknumber = thisvalue + 1
TxtB_Number.Value = checknumber
ResetTotal
ResetDate
'test:
'***************************************************
'***************************************************
'***************************************************
'***************************************************
'Stop everything and reset, or display error message.
StopActiveButton:
Sheets("gas").Select
Range("a1").Select
TxtB_GasTotal = Selection.Value
ResetDate
AutoFitChecks
hideColumns
'Range("A1").Value = Val(tbxInput)
Sheets("checking").Select
TxTB_Amount = ""
GoTo TheEnd
errormsg:
donothing = MsgBox("You forgot to check one of the checkboxes.", vbCritical)
GoTo TheEnd
errormsg2:
donothing = MsgBox("Click only ONE checkbox.", vbCritical)
ChkB_Checks = False
ChkB_Pulse = False
ChkB_Gas = False
ChkB_Void = False
TheEnd:
Sheets("checking").Select
'Range("a1").Select
'e_u
'Sheets("matrix").Select
'e_u
'Sheets("checking").Select
End Sub
Sub ResetDate()
With CmB_Date
.SelStart = InStr(.Text, "/")
.SelLength = Len(.Text) - .SelStart
.SetFocus
End With
End Sub
Private Sub Cmbt_SaveCheckbook_Click()
Save
Application.WindowState = xlMaximized
TestUserForm.hide
End Sub
Private Sub CommandButton3_Click()
Sheets("budget").Select
If Budget_Cbx_Months_15 = "January" Then GoTo CopJan
End
CopJan:
Jan15Total = Range("K4").Value
Range("D6:D75").Select
Selection.Copy
Range("k6:k75").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("K5").Select
Selection.Value = "1b"
GoTo endcheckbudget
'Range("A2:a13").Select
' Selection.Copy
' mover 1
' ActiveSheet.Paste
endcheckbudget:
AutoFitChecks
End Sub
Private Sub CommandButton5_Click()
AutoFitChecks
Range("A1").Select
'Save
'AllUp
Application.WindowState = xlMaximized
Unload Me
answerthis = MsgBox("Do you want to close AND SAVE the Checkbook?", vbYesNo)
If answerthis = 6 Then
'ThisWorkbook.Save
Save
Application.DisplayAlerts = False
Application.Quit
End If
Application.WindowState = xlMaximized
Unload Me
End Sub
Private Sub CommandButton6_Click()
Save
End Sub
Private Sub DepFrm_Cmbt_Enter_Click()
hideColumns
Range("c500").Select
e_u
moved 1
movel 1
'Range("A1").Value = Val(tbxInput)
Selection.Value = Cmb_DepDate.Value
mover 1
Selection.Value = TxB_DepAmount.Value
'Selection.Value = TextBox1
Sheets("checking").Select
AutoFitChecks
hideColumns
'Range("A1").Value = Val(tbxInput)
'TxB_DepAmount
ResetTotal
End Sub
Private Sub Deposites_Click()
AutoFitChecks
End Sub
Sub tst()
Dim LandAmount As Variant
Sheets("budget").Select
LandAmount = Range("j6").Value
With Land
.AddItem Amount
.AddItem "paid"
End With
CmB_Description.Value = "WalMart"
AutoFitChecks
End Sub
Private Sub MPageBudget_Change()
Sheets("budget").Select
End Sub
Private Sub EndingFrm_TxB_Close_Click()
Unload Me
End Sub
Sub MultiPage1_Change()
Sheets("checking").Select
End Sub
Private Sub TxtBTest_Change()
Sheets("checking").Select
Range("a1").Select
e_d
thisvalue = Selection.Value
moved 1
checknumber = thisvalue + 1
TxtB_Number.Value = checknumber
AutoFitChecks
End Sub
Private Sub TxB_DepAmount_Change()
End Sub
Private Sub TxB_Total_Change()
End Sub
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
'thismonthvalue = DateCMB.Text
Sheets("matrix").Visible = False
'List of Sheets:
'
'Needed to balance statement
'Checking
'CSV
'csv_bkup
'sort_csv
'Not_there
'Header
'end
'CurrentMonth
'Backup
'Test_checkbook
'Needed to update checkbook
'Checking
'Budget
'savings
'deps
'gas
'home_insurance
'addresses
'paydays
'Header
'*************************************
'Application.Caption = "Balancing ChkBook's Checking Account"
'Show needed Sheets
Sheets("header").Select
For Each HiddenSheet In ActiveWorkbook.Sheets
HiddenSheet.Visible = True
Next HiddenSheet
ActiveWindow.SelectedSheets.Visible = False
Sheets("header").Visible = True
Sheets("checking").Select
'Take out unneeded sheets
' Sheets("budget").Visible = False
' Sheets("gas").Visible = False
' Sheets("savings").Visible = False
' Sheets("deps").Visible = False
' Sheets("addresses").Visible = False
' Sheets("paydays").Visible = False
' Sheets("test_checkbook").Visible = False
' Sheets("home_insurance").Visible = False
'DateCMB.Value = thismonthvalue
Sheets("checking").Select
hideColumns
With Budget_Cbx_Months_15
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
With TxB_DepAmount
.AddItem "1407.88"
.AddItem "1248.76"
End With
TxB_DepAmount.Value = ""
With Cmb_DepDate
.AddItem "1/"
.AddItem "2/"
.AddItem "3/"
.AddItem "4/"
.AddItem "5/"
.AddItem "6/"
.AddItem "7/"
.AddItem "8/"
.AddItem "9/"
.AddItem "10/"
.AddItem "11/"
.AddItem "12/"
End With
With Cmb_DepDate
.AddItem "1/"
.AddItem "2/"
.AddItem "3/"
.AddItem "4/"
.AddItem "5/"
.AddItem "6/"
.AddItem "7/"
.AddItem "8/"
.AddItem "9/"
.AddItem "10/"
.AddItem "11/"
.AddItem "12/"
End With
With CmB_Date
.AddItem "1/"
.AddItem "2/"
.AddItem "3/"
.AddItem "4/"
.AddItem "5/"
.AddItem "6/"
.AddItem "7/"
.AddItem "8/"
.AddItem "9/"
.AddItem "10/"
.AddItem "11/"
.AddItem "12/"
End With
'CmB_Date.Value = ""
With CmB_Description
.AddItem "WalMart"
.AddItem "Krogers"
.AddItem "USAA"
.AddItem "AEP"
.AddItem "Direct TV"
.AddItem "Land"
.AddItem "JetBB"
.AddItem "Suntrust"
.AddItem "Cap One"
.AddItem "Juniper"
.AddItem "Sprint"
.AddItem "Embarq"
.AddItem "Amer Express"
.AddItem "Toyota"
.AddItem "Church"
End With
With Budget_Cbx_Months
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
CmB_Description.Value = "WalMart"
TxTB_Amount.Value = "0.00"
'TxB_DepAmount.Value = "0.00"
ResetDate
AutoFitChecks
'Reset check number
Sheets("checking").Select
Range("e500").Select
e_u
thisvalue = Selection.Value
moved 1
If thisvalue = "end" Then
TxtB_Number.Value = 1000
GoTo SkipThis
End If
checknumber = thisvalue + 1
TxtB_Number.Value = checknumber
'reset Total
SkipThis:
Sheets("checking").Select
Range("b6").Select
TxB_Total.Value = Selection.Value
Sheets("gas").Select
Range("a1").Select
TxtB_GasTotal = Selection.Value
Sheets("checking").Select
AutoFitChecks
Sheets("checking").Select
Application.ScreenUpdating = True
End Sub
Sub pulse()
Range("k500").Select
e_u
If IsEmpty(ActiveCell) = False Then
moved 1
movel 1
End If
Selection.Value = CmB_Date.Value
mover 1
Selection.Value = TxTB_Amount.Value
mover 2
Selection.Value = CmB_Description.Value
'mover 1
'Selection.Value = CmB_Description.Value
Sheets("checking").Select
Range("b6").Select
TxB_Total.Value = Selection.Value
Range("a1").Select
ResetDate
AutoFitChecks
End Sub
Sub Gas()
Sheets("gas").Select
Range("c500").Select
e_u
If IsEmpty(ActiveCell) = False Then
moved 1
End If
movel 1
Selection.Value = CmB_Date.Value
mover 1
Selection.Value = TxTB_Amount.Value
'mover 1
'Selection.Value = CmB_Description.Value
Sheets("checking").Select
Sheets("checking").Select
Range("b6").Select
TxB_Total.Value = Selection.Value
ResetDate
AutoFitChecks
ChkB_Gas = False
Range("a1").Select
'moveu 600
pulse
End Sub
Sub ResetTotal()
'Reset Total
Sheets("checking").Select
Range("b6").Select
TxB_Total.Value = Selection.Value
End Sub