Excel理财收支管理代码

Excel收支管理系统程序功能：银行信息记录存款利息计算银行总资金汇总银行年收益计算收支记录收入项目记录，增加到银行账户支出项目记录，选择支出账户可对每条记录进行修改，并与账户关联收支项目管理，可增加或删除收支项目本程序操作灵活，界面人性化，比如删除“银行记录”金额，可将本条记录信息全部删除（需要确认）；收支记录中信息输入完整，自动与银行账户信息关联；可自己添加银行并修改利率。使用本程序可快速判别存款方式对收益的影响，比如5万存入工商银行：整存整取两年，利息4400整存整取一年，利息3561（两年后取）现在银行利率也有差别，存不同银行收益相差多少也能方便了解。界面“银行记录”“银行记录”中复制代码如下：PrivateSubCalendar1_Click()ActiveCell=Calendar1Calendar1.Visible=FalseEndSubPrivateSubWorksheet_SelectionChange(ByValTargetAsRange)DimlvDimzhuancun(1To100)Dimlv_huo(1To1000)Dimlv_ding1_3(1To1000)Dimlv_ding1_6(1To1000)Dimlv_ding1_12(1To1000)Dimlv_ding1_24(1To1000)Dimlv_ding1_36(1To1000)Dimlv_ding1_60(1To1000)Dimlv_ding2_12(1To1000)Dimlv_ding2_36(1To1000)Dimlv_ding2_60(1To1000)DimrngAsRangern=Range("b65536").End(xlUp).Row'最大行号cn=Range("b2").End(xlToRight).Column'最大列号Application.ScreenUpdating=False'数据初始化IfSheet1.Cells(ActiveCell.Row,1)=""AndSheet1.Cells(ActiveCell.Row,3)=""AndSheet1.Cells(ActiveCell.Row,2)<>""ThenSheet1.Cells(ActiveCell.Row,1)="中国银行"EndIfFory=3TornIfSheet1.Cells(y,1)<>""ThenSheet4.Select'查找银行名称Setrng=Sheet4.[B:B].Find(Sheet1.Cells(y,1))'定位银行IfNotrngIsNothingThen'rng.Font.ColorIndex=3'颜色暂不设置Application.GotoReference:=rng.Address(,,xlR1C1)EndIfEndIflv_huo(y)=Sheet4.Cells(ActiveCell.Row+3,ActiveCell.Column+1)lv_ding1_3(y)=Sheet4.Cells(ActiveCell.Row+6,ActiveCell.Column+1)lv_ding1_6(y)=Sheet4.Cells(ActiveCell.Row+7,ActiveCell.Column+1)lv_ding1_12(y)=Sheet4.Cells(ActiveCell.Row+8,ActiveCell.Column+1)lv_ding1_24(y)=Sheet4.Cells(ActiveCell.Row+9,ActiveCell.Column+1)lv_ding1_36(y)=Sheet4.Cells(ActiveCell.Row+10,ActiveCell.Column+1)lv_ding1_60(y)=Sheet4.Cells(ActiveCell.Row+11,ActiveCell.Column+1)lv_ding2_12(y)=Sheet4.Cells(ActiveCell.Row+13,ActiveCell.Column+1)lv_ding2_36(y)=Sheet4.Cells(ActiveCell.Row+14,ActiveCell.Column+1)lv_ding2_60(y)=Sheet4.Cells(ActiveCell.Row+15,ActiveCell.Column+1)'返回sheet“银行项目”Sheet1.SelectNext'--------------'格式初始化WithRange(Sheet1.Cells(3,1),Sheet1.Cells(rn+30,cn)).Interior.Pattern=xlNone.TintAndShade=0.PatternTintAndShade=0EndWith'---------------'取消列表WithSheet1.Range("A:A").Validation.Delete.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,Operator_:=xlBetween.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithWithSheet1.Range("C:C").Validation.Delete.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,Operator_:=xlBetween.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'银行列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("A3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'项目列表WithRange("C3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:="活期,整存整取,整存零取,零存整取,存本取息,定活两便".IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithForZ=3Torn'银行列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("A"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'项目列表WithRange("C"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:="活期,整存整取,整存零取,零存整取,存本取息,定活两便".IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'-----------------------'年利率IfSheet1.Cells(Z,3)="Huo"OrSheet1.Cells(Z,3)="活期"Thenlv=lv_huo(Z)Sheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)="ZZ"OrSheet1.Cells(Z,3)="整存整取"ThenIfSheet1.Cells(Z,5)<6Thenlv=lv_ding1_3(Z)'3个月EndIfIfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Thenlv=lv_ding1_6(Z)'半年EndIfIfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Thenlv=lv_ding1_12(Z)'1年EndIfIfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Thenlv=lv_ding1_24(Z)'2年EndIfIfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Thenlv=lv_ding1_36(Z)'3年EndIfIfSheet1.Cells(Z,5)>=60Thenlv=lv_ding1_60(Z)'5年EndIfSheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)="ZLLZBX"OrSheet1.Cells(Z,3)="零存整取"OrSheet1.Cells(Z,3)="整存零取"OrSheet1.Cells(Z,3)="存本取息"ThenIfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<36Thenlv=lv_ding2_12(Z)'1年EndIfIfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Thenlv=lv_ding2_36(Z)'3年EndIfIfSheet1.Cells(Z,5)>=60Thenlv=lv_ding2_60(Z)'5年EndIfSheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)="定活两便"ThenIfSheet1.Cells(Z,4)=""AndSheet1.Cells(Z,5)=""Thendh=MsgBox("未区分各家银行计算方法，结果不一定准确，按利率60%计算？",vbYesNo,"提示")Ifdh=vbYesThenIfSheet1.Cells(Z,5)<6Thenlv=lv_ding1_3(Z)'3个月EndIfIfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Thenlv=lv_ding1_6(Z)'半年EndIfIfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Thenlv=lv_ding1_12(Z)'1年EndIfIfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Thenlv=lv_ding1_24(Z)'2年EndIfIfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Thenlv=lv_ding1_36(Z)'3年EndIfIfSheet1.Cells(Z,5)>=60Thenlv=lv_ding1_60(Z)'5年EndIflv=lv*0.6ElseSheet1.Cells(Z,3)=""EndIfElseIfSheet1.Cells(Z,5)<6Thenlv=lv_ding1_3(Z)'3个月EndIfIfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Thenlv=lv_ding1_6(Z)'半年EndIfIfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Thenlv=lv_ding1_12(Z)'1年EndIfIfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Thenlv=lv_ding1_24(Z)'2年EndIfIfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Thenlv=lv_ding1_36(Z)'3年EndIfIfSheet1.Cells(Z,5)>=60Thenlv=lv_ding1_60(Z)'5年EndIflv=lv*0.6EndIfSheet1.Cells(Z,6)=lvEndIf'-------------------------------------------OnErrorResumeNextIfTarget.Column=4AndTarget.Value<>"存入日期"AndTarget.Row>2AndTarget.Row<=rnThenCalendar1.Visible=True'日历控件Calendar1.Left=Cells(Target.Row,4).LeftCalendar1.Top=Cells(Target.Row+1,4).TopWithCalendar1'当前日期.Year=Year(Now).Month=Month(Now).Day=Day(Now)EndWithElseIfTarget.Column=1AndTarget.Row=1ThenCalendar1.Visible=True'日历控件Calendar1.Left=Cells(Target.Row,1).LeftCalendar1.Top=Cells(Target.Row+1,1).TopWithCalendar1'当前日期.Year=Year(Now).Month=Month(Now).Day=Day(Now)EndWithElseCalendar1.Visible=FalseEndIfOnErrorResumeNextnian=Int(Sheet1.Cells(Z,5)/12)'年数yue=Sheet1.Cells(Z,5)-12*nian'月数lixishui=0'利息税率daoqi=Sheet1.Cells(Z,2)+Sheet1.Cells(Z,2)*(lv/100)*(Sheet1.Cells(Z,5)/12)*(1-lixishui)'到期总数'当前日期与存入日期相差月数IfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))>=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))>=Day(Sheet1.Cells(Z,4))Thenm=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12+Month(Sheet1.Cells(1,1))-Month(Sheet1.Cells(Z,4))EndIfIfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))>=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))<Day(Sheet1.Cells(Z,4))Thenm=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12+Month(Sheet1.Cells(1,1))-Month(Sheet1.Cells(Z,4))-1EndIfIfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))<Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))>=Day(Sheet1.Cells(Z,4))Thenm=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12-Month(Sheet1.Cells(1,1))+Month(Sheet1.Cells(Z,4))EndIfIfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))<Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))>=Day(Sheet1.Cells(Z,4))Thenm=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12-Month(Sheet1.Cells(1,1))+Month(Sheet1.Cells(Z,4))-1EndIfIfYear(Sheet1.Cells(1,1))<Year(Sheet1.Cells(Z,4))Thenm=0EndIfIfYear(Sheet1.Cells(1,1))=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))<Month(Sheet1.Cells(Z,4))Thenm=0EndIfIfYear(Sheet1.Cells(1,1))=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))<Day(Sheet1.Cells(Z,4))Thenm=0EndIf'----------------------------zhuan=Int(m/Sheet1.Cells(Z,5))-1'转存次数Ifzhuan<0Thenzhuan=0EndIf'数据写入IfZ>2AndSheet1.Cells(Z,6)<>""ThenSheet1.Cells(Z,7)=daoqi'---到期总数IfSheet1.Cells(Z,4)<>""ThenSheet1.Cells(Z,8).FormulaR1C1=_"=DATE(YEAR(RC[-4]),MONTH(RC[-4])+RC[-3],DAY(RC[-4]))"'---到期日期EndIfEndIfIfSheet1.Cells(Z,7)<>""ThenSheet1.Cells(Z,9)=(Sheet1.Cells(Z,7)-Sheet1.Cells(Z,2))'---利息ElseSheet1.Cells(Z,9)=""EndIfSheet1.Cells(Z,10)=zhuan'---转存次数zhuancun(1)=Sheet1.Cells(Z,7)+Sheet1.Cells(Z,7)*(lv/100)*(Sheet1.Cells(Z,5)/12)*(1-lixishui)'第一次转存到期总数Ifzhuan>=2ThenFori=2Tozhuanzhuancun(i)=zhuancun(i-1)+zhuancun(i-1)*(lv/100)*(Sheet1.Cells(Z,5)/12)*(1-lixishui)NextEndIfIfInt(m/Sheet1.Cells(Z,5))=m/Sheet1.Cells(Z,5)ThenSheet1.Cells(Z,11).FormulaR1C1=_"=DATE(YEAR(RC[-7]),MONTH(RC[-7])+RC[-6]*(RC[-1]+1),DAY(RC[-7]))"'---转存到期日期ElseSheet1.Cells(Z,11).FormulaR1C1=_"=DATE(YEAR(RC[-7]),MONTH(RC[-7])+RC[-6]*(RC[-1]+1),DAY(RC[-7]))"'---转存到期日期EndIfIfzhuan<1ThenSheet1.Cells(Z,10)=""Sheet1.Cells(Z,11)=""Sheet1.Cells(Z,12)=Sheet1.Cells(Z,7)Sheet1.Cells(Z,13)=Sheet1.Cells(Z,9)ElseSheet1.Cells(Z,12)=zhuancun(zhuan)'---当前总额Sheet1.Cells(Z,13)=Sheet1.Cells(Z,12)-Sheet1.Cells(Z,2)'---当前利息EndIf'-------------------------------------'格式IfZMod2=1Then'当前行数除以2的余数为1（奇数）WithRange(Sheet1.Cells(Z,1),Sheet1.Cells(Z,14)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=10198015.TintAndShade=0.PatternTintAndShade=0EndWithElseIfZMod2=0Then'当前行数除以2的余数为0（偶数）WithRange(Sheet1.Cells(Z,1),Sheet1.Cells(Z,14)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=13421823.TintAndShade=0.PatternTintAndShade=0EndWithEndIf'---------------------------------------'未选择银行不计利率IfSheet1.Cells(Z,1)=""Thenlv=0Sheet1.Cells(Z,6)=""Sheet1.Cells(Z,7)=""Sheet1.Cells(Z,9)=""EndIf'活期不考虑到期时间，按存入时间到当前日期利息计算IfSheet1.Cells(Z,3)="活期"Thentian=Date-Sheet1.Cells(Z,4)nian1=Int(tian/365)'年数yue1=Sheet1.Cells(Z,5)-12*nian'月数Sheet1.Cells(Z,7)=Sheet1.Cells(Z,2)+Sheet1.Cells(Z,2)*lv/100*(tian/365)Sheet1.Cells(Z,9)=Sheet1.Cells(Z,7)-Sheet1.Cells(Z,2)Sheet1.Cells(Z,5)=""Sheet1.Cells(Z,6)=lvSheet1.Cells(Z,8)=""Sheet1.Cells(Z,10)=""Sheet1.Cells(Z,11)=""Sheet1.Cells(Z,12)=Sheet1.Cells(Z,7)Sheet1.Cells(Z,13)=Sheet1.Cells(Z,9)EndIf'未选择存入时间IfSheet1.Cells(Z,4)=""ThenSheet1.Cells(Z,9)=""Sheet1.Cells(Z,10)=""Sheet1.Cells(Z,11)=""Sheet1.Cells(Z,13)=""Sheet1.Cells(Z,7)=Sheet1.Cells(Z,2)Sheet1.Cells(Z,12)=Sheet1.Cells(Z,2)EndIf'到期提示IfSheet1.Cells(Z,8)<Sheet1.Cells(1,1)ThenWithSheet1.Cells(Z,8).Font.Color=-65281.TintAndShade=0EndWithElseWithSheet1.Cells(Z,8).Font.ThemeColor=xlThemeColorLight1.TintAndShade=0EndWithEndIfIfSheet1.Cells(Z,11)<Sheet1.Cells(1,1)ThenWithSheet1.Cells(Z,11).Font.Color=-65281.TintAndShade=0EndWithElseWithSheet1.Cells(Z,11).Font.ThemeColor=xlThemeColorLight1.TintAndShade=0EndWithEndIfNext'------------------------------------------------------------------Forj=3Torn+1000IfSheet1.Cells(j,2)=""AndSheet1.Cells(j,3)<>""Thensc=MsgBox("确认删除该项目？",vbYesNo,"提示")Ifsc=vbYesThenSheet1.Cells(j,1)=""Sheet1.Cells(j,3)=""Sheet1.Cells(j,4)=""Sheet1.Cells(j,5)=""Sheet1.Cells(j,6)=""Sheet1.Cells(j,7)=""Sheet1.Cells(j,8)=""Sheet1.Cells(j,9)=""Sheet1.Cells(j,10)=""Sheet1.Cells(j,11)=""Sheet1.Cells(j,12)=""Sheet1.Cells(j,13)=""Sheet1.Cells(j,14)=""EndIfRows(j).DeleteEndIfNext'图表数据处理'图表1，饼图，显示各银行资金分布Sheet6.Columns("A:B").ClearSheet6.Cells(1,1)="图表1"zong=0ben=0nianli=0ForK=3Tornrn1=Sheet6.Range("a65536").End(xlUp).RowIfrn1<2Thenrn1=2EndIfflag=0Fork1=3Torn1IfSheet1.Cells(K,1)=Sheet6.Cells(k1,1)Thenflag=flag+1Sheet6.Cells(k1,2)=Sheet6.Cells(k1,2)+Sheet1.Cells(K,12)EndIfNextIfflag=0ThenSheet6.Cells(rn1+1,1)=Sheet1.Cells(K,1)Sheet6.Cells(rn1+1,2)=Sheet1.Cells(K,12)EndIfzong=zong+Sheet1.Cells(K,12)ben=ben+Sheet1.Cells(K,2)nianli=nianli+(Sheet1.Cells(K,13)/(Sheet1.Cells(K,5)+Sheet1.Cells(K,5)*Sheet1.Cells(K,10)))*12NextSheet1.Cells(1,15)="银行总额:"&Format(zong,"#,##0.0")Sheet1.Cells(2,15)=Application.Text(Format(zong,"###0"),"[DBNum2]")'大写Sheet1.Cells(3,15)="本金："&Application.Text(Format(ben,"###0"),"[DBNum2]")'大写Sheet1.Cells(4,15)="年收益："&Application.Text(Format(nianli,"###0"),"[DBNum1]")&"["&Format(nianli,"###0")&"]"'大写'图表设置r=ActiveCell.Rowc=ActiveCell.Column'Range("A1:C"&5&",E1:G"&5&"")等价于Range("A1:C5,E1:G5")rn1=Sheet6.Range("a65536").End(xlUp).RowIfrn1>=2ThenActiveSheet.ChartObjects("图表1").ActivateActiveChart.SetSourceDataSource:=Sheet6.Range("A2:A"&rn1&",B2:B"&rn1&"")Sheet1.Cells(r,c).Select'设置绘图区格式WithSheets(1).ChartObjects("图表1").Name.Left.Top.Height=250.Width.Chart.ChartTypeEndWithEndIfWithRange("O3","O4").HorizontalAlignment=xlRight.VerticalAlignment=xlCenter.WrapText=False.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWithWithRange("O3").Font.Color=-16751104.TintAndShade=0EndWithWithRange("O4").Font.Color=-3368704.TintAndShade=0EndWithRange("O3","O4").Font.Size=9EndSub

界面“收支记录”“收支记录”中复制代码如下：DimriDimr(0To1)Dimr1(0To1)Dimr2(0To1)Dimr3(0To1)Dimr10(0To1)Dimr11(0To1)Dimr12(0To1)Dimr13(0To1)DimkongDimkong1Dimkong2Dimkong10Dimkong11Dimkong12Dimflag_shanDimflag_shan10PrivateSubCalendar1_Click()ActiveCell=Calendar1Calendar1.Visible=FalseEndSubPrivateSubWorksheet_SelectionChange(ByValTargetAsRange)ri=ri+1'ri定义为鼠标点击次数rik=riMod2'rik为0或1rns=Range("B65536").End(xlUp).Row'收入最大行号rnz=Range("L65536").End(xlUp).Row'支出最大行号Ifrns>=rnzThenrn=rnsElsern=rnzEndIfcns=Range("B2").End(xlToRight).Column'最大列号cnz=Range("L2").End(xlToRight).Column'最大列号'格式初始化WithRange(Sheet2.Cells(3,1),Sheet2.Cells(rn+10,cnz)).Interior.Pattern=xlNone.TintAndShade=0.PatternTintAndShade=0EndWith'---------------WithSheet2.Range("A:N").Validation.Delete.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,Operator_:=xlBetween.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'收入项目列表更新rn7s=Sheet7.Range("A65536").End(xlUp).Rowf="=收支项目!A2:A"&rn7s&""WithRange("B3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'支出项目列表更新rn7z=Sheet7.Range("C65536").End(xlUp).Rowf="=收支项目!C2:C"&rn7z&""WithRange("L3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'收入账户列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("D3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'支出账户列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("n3").Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithForZ=2Torn'收入项目列表更新rn7s=Sheet7.Range("A65536").End(xlUp).Rowf="=收支项目!A2:A"&rn7s&""WithRange("B"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'支出项目列表更新rn7z=Sheet7.Range("C65536").End(xlUp).Rowf="=收支项目!C2:C"&rn7z&""WithRange("L"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'收入账户列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("D"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'支出账户列表更新rn4=Sheet4.Range("e65536").End(xlUp).Rowf="=基本信息!E5:E"&rn4&""WithRange("N"&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle="".ErrorTitle="".InputMessage="".ErrorMessage="".IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithOnErrorResumeNextIfTarget.Column=1AndTarget.Value<>"日期"AndTarget.Row>2AndTarget.Row<=rn+1ThenCalendar1.Visible=True'日历控件Calendar1.Left=Cells(Target.Row,1).LeftCalendar1.Top=Cells(Target.Row+1,1).TopWithCalendar1'当前日期.Year=Year(Now).Month=Month(Now).Day=Day(Now)EndWithElseIfTarget.Column=11AndTarget.Value<>"日期"AndTarget.Row>2AndTarget.Row<=rn+1ThenCalendar1.Visible=True'日历控件Calendar1.Left=Cells(Target.Row,11).LeftCalendar1.Top=Cells(Target.Row+1,11).TopWithCalendar1'当前日期.Year=Year(Now).Month=Month(Now).Day=Day(Now)EndWithElseCalendar1.Visible=FalseEndIf'格式IfZMod2=1Then'当前行数除以2的余数为1（奇数）IfSheet2.Cells(Z+1,1)<>""OrSheet2.Cells(Z+1,2)<>""ThenWithRange(Sheet2.Cells(Z+1,1),Sheet2.Cells(Z+1,cns)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=13421823.TintAndShade=0.PatternTintAndShade=0EndWithEndIfIfSheet2.Cells(Z+1,11)<>""OrSheet2.Cells(Z+1,12)<>""ThenWithRange(Sheet2.Cells(Z+1,11),Sheet2.Cells(Z+1,cnz)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=13421823.TintAndShade=0.PatternTintAndShade=0EndWithEndIfElseIfZMod2=0Then'当前行数除以2的余数为0（偶数）IfSheet2.Cells(Z+1,1)<>""OrSheet2.Cells(Z+1,2)<>""ThenWithRange(Sheet2.Cells(Z+1,1),Sheet2.Cells(Z+1,cns)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=10198015.TintAndShade=0.PatternTintAndShade=0EndWithEndIfIfSheet2.Cells(Z+1,11)<>""OrSheet2.Cells(Z+1,12)<>""ThenWithRange(Sheet2.Cells(Z+1,11),Sheet2.Cells(Z+1,cnz)).Interior.Pattern=xlSolid.PatternColorIndex=xlAutomatic.Color=10198015.TintAndShade=0.PatternTintAndShade=0EndWithEndIfEndIf'---------------------------------------Next'-------------------------收入-----------------------------------------Ifrik=0Thenr2(0)=Range("b65536").End(xlUp).Row'某次点击收入项目最大行号EndIfIfrik=1Thenr2(1)=Range("b65536").End(xlUp).Row'下一次点击收入项目最大行号EndIf'增加行、减少行判断，用于记录被删除行信息Ifrik=0ThenIfr2(0)>r2(1)Thenzeng2="增加行"EndIfIfr2(0)<r2(1)Thenzeng2="减少行"EndIfEndIfIfrik=1ThenIfr2(1)>r2(0)Thenzeng2="增加行"EndIfIfr2(1)<r2(0)Thenzeng2="减少行"EndIfEndIfIfzeng2="减少行"Thenflag_shan="可删除"EndIf'删除收入记录Forj=3Torn+1000'记录被删除项目信息IfSheet2.Cells(j,1)<>""AndSheet2.Cells(j,2)=""AndSheet2.Cells(j,3)<>""AndSheet2.Cells(j,4)<>""ThenIfflag_shan="可删除"Thens2=Sheet2.Cells(j,2)s3=Sheet2.Cells(j,3)s4=Sheet2.Cells(j,4)