1、Excel收支支管理系統程序功能：銀行信息記錄存款利息計算銀行總資金匯總總銀行年收益計算算收支記錄收入項目記錄，增增加到銀行賬賬戶支出項目記錄，選選擇支出賬戶戶可對每條記錄進進行修改，并并與賬戶關聯聯收支項目管理，可可增加或刪除除收支項目本程序操作靈活活，界面人性性化，比如刪刪除“銀行記記錄”金額，可可將本條記錄錄信息全部刪刪除（需要確確認）；收支支記錄中信息息輸入完整，自自動與銀行賬賬戶信息關聯聯；可自己添添加銀行并修修改利率。使用本程序可快快速判別存款款方式對收益益的影響，比比如5萬存入工商商銀行：整存整取兩年，利利息44000整存整取一年，利利息35611（兩年后取取）現在銀行利率也也有

2、差別，存存不同銀行收收益相差多少少也能方便了了解。界面“銀行記錄錄”“銀行記錄”中中復制代碼如如下：Privatee Sub Calenndar1_Clickk()ActiveCCell = Caleendar11Calendaar1.Viisiblee = FaalseEnd SubbPrivatee Sub Workssheet_SelecctionCChangee(ByVall Targget Ass Rangge)Dim lvDim zhuuancunn(1 To 100)Dim lv_huo(1 To 1000)Dim lv_ding11_3(1 Too 10000)Dim lv_d

3、ing11_6(1 Too 10000)Dim lv_ding11_12(1 TTo 10000)Dim lv_ding11_24(1 TTo 10000)Dim lv_ding11_36(1 TTo 10000)Dim lv_ding11_60(1 TTo 10000)Dim lv_ding22_12(1 TTo 10000)Dim lv_ding22_36(1 TTo 10000)Dim lv_ding22_60(1 TTo 10000)Dim rngg As RRangern = Raange(b655336).EEnd(xllUp).RRow 最最大行號cn = Raange(b2).

4、End(xxlToRiight).Colummn 最最大列號Applicaation.ScreeenUpdaating = Fallse數據初始化If Sheeet1.Ceells(AActiveeCell.Row, 1) = Annd Sheeet1.CCells(ActivveCelll.Row, 3) = AAnd Shheet1.Cellss(ActiiveCelll.Roww, 2) Thenn Sheeet1.CCells(ActivveCelll.Row, 1) = 中國銀銀行End IfFor y = 3 Too rn If Sheett1.Cellls(y, 1) Then

5、Sheeet4.Seelect 查找銀銀行名稱 Set rng = Sheett4.B:B.Findd(Sheeet1.Ceells(yy, 1) 定位銀銀行 If NNot rnng Is Nothiing Thhen rng.Font.ColorrIndexx = 3 顏顏色暫不設置置 Appliicatioon.Gotto Refferencce:=rng.Addreess(, , xlRR1C1) End If Endd If lv_huo(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 33, ActtiveCeell.Coolumn + 1) l

6、v_ding11_3(y) = Shheet4.Cellss(ActiiveCelll.Roww + 6, ActiiveCelll.Collumn + 1) lv_ding11_6(y) = Shheet4.Cellss(ActiiveCelll.Roww + 7, ActiiveCelll.Collumn + 1) lv_ding11_12(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 88, ActtiveCeell.Coolumn + 1) lv_ding11_24(yy) = SSheet44.Cellls(ActtiveCeell.Roow +

7、 99, ActtiveCeell.Coolumn + 1) lv_ding11_36(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 110, AcctiveCCell.CColumnn + 1) lv_ding11_60(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 111, AcctiveCCell.CColumnn + 1) lv_ding22_12(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 113, AcctiveCCell.CColumnn + 1) lv_ding2

8、2_36(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 114, AcctiveCCell.CColumnn + 1) lv_ding22_60(yy) = SSheet44.Cellls(ActtiveCeell.Roow + 115, AcctiveCCell.CColumnn + 1) 返回回sheett“銀行項目目” Sheeet1.SSelecttNext-格式初始化With Raange(SSheet11.Cellls(3, 1), SSheet11.Cellls(rn + 330, cnn).Innterioor .Paatternn = x

9、llNone .TiintAnddShadee = 0 .PaatternnTintAAndShaade = 0End Witth-取消列表 Witth Sheeet1.RRange(A:A).Vallidatiion .Dellete .Addd Typee:=xlVaalidatteInpuutOnlyy, AlerrtStylle:=xlVaalidAllertSttop, OOperattor _ :=xlBeetweenn .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle =

10、 .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh Witth Sheeet1.RRange(C:C).Vallidatiion .Dellete .Addd Typee:=xlVaalidatteInpuutOnlyy, AlerrtStylle:=xlVaalidAllertSttop, OOperattor _ :=xlBeetweenn .IgnnoreBllank = Truee .InC

11、CellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh 銀行列表更更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(AA3).VValidaation .Dellete .Ad

12、dd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue

13、 Endd Withh項目列表 Witth Rannge(CC3).VValidaation .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=活期期,整存整取,整存零取,零存整取,存本取息,定活兩便 .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorM

14、esssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd WithhFor Z = 3 Too rn 銀行行列表更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(AA & ZZ + 1).Valiidatioon .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllert

15、Sttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh 項目目列表 Witth Rannge(CC & ZZ + 1).Valiidatioon

16、.Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=活期期,整存整取,整存零取,零存整取,存本取息,定活兩便 .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .S

17、hoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh - 年利利率 If Sheett1.Cellls(Z, 3) = Huoo Or Sheett1.Cellls(Z, 3) = 活期 Thhen lv = lv_huo(ZZ) Sheeet1.Ceells(ZZ, 6) = lv Endd If If Sheett1.Cellls(Z, 3) = ZZ Or SSheet11.Cellls(Z, 3) = 整存整取取 Theen If SSheet11.Cellls(Z, 5) = 6 Annd Sheeet1.CCells(Z, 5) =

18、12 AAnd Shheet1.Cellss(Z, 55) = 24 AAnd Shheet1.Cellss(Z, 55) = 36 AAnd Shheet1.Cellss(Z, 55) = 60 TThen lv = llv_dinng1_600(Z) 5年 End If Sheeet1.Ceells(ZZ, 6) = lv Endd If If Sheett1.Cellls(Z, 3) = ZL LZ BXX Or Sheett1.Cellls(Z, 3) = 零存整整取 Orr Sheeet1.Ceells(ZZ, 3) = 整存存零取 OOr Sheeet1.CCells(Z, 3)

19、= 存存本取息 Then If SSheet11.Cellls(Z, 5) = 12 AAnd Shheet1.Cellss(Z, 55) = 36 AAnd Shheet1.Cellss(Z, 55) = 60 TThen lv = llv_dinng2_600(Z) 5年 End If Sheeet1.Ceells(ZZ, 6) = lv Endd If If Sheett1.Cellls(Z, 3) = 定活兩兩便 Thhen If SSheet11.Cellls(Z, 4) = Annd Sheeet1.CCells(Z, 5) = Thenn dh = MsgBoox(未區區分各家銀

20、行行計算方法，結結果不一定準準確，按利率率60%計算？, vbbYesNoo, 提示示) If dhh = vbbYes TThen IIf Sheeet1.CCells(Z, 5) = 66 And Sheett1.Cellls(Z, 5) = 112 Andd Sheeet1.Ceells(ZZ, 5) = 224 Andd Sheeet1.Ceells(ZZ, 5) = 336 Andd Sheeet1.Ceells(ZZ, 5) = 660 Theen lvv = lvv_dingg1_60(Z) 5年 EEnd Iff llv = lv * 00.6 Else SSheet11.Ce

21、llls(Z, 3) = End IIf Elsee If Shheet1.Cellss(Z, 55) = 6 Andd Sheeet1.Ceells(ZZ, 5) = 12 Annd Sheeet1.CCells(Z, 5) = 24 Annd Sheeet1.CCells(Z, 5) = 36 Annd Sheeet1.CCells(Z, 5) = 60 Thhen llv = llv_dinng1_600(Z) 5年 End IIf lv = lv * 00.6 End If Sheeet1.Ceells(ZZ, 6) = lv Endd If - On Errorr Resuume N

22、eext If Targeet.Collumn = 4 Annd Tarrget.VValue 存存入日期 And TTargett.Row 2 AAnd Taarget.Row = Yeear(Shheet1.Cellss(Z, 44) Annd Monnth(Shheet1.Cellss(1, 11) = Montth(Sheeet1.CCells(Z, 4) Andd Day(Sheett1.Cellls(1, 1) = Daay(Sheeet1.CCells(Z, 4) Theen m = (Yearr(Sheeet1.Ceells(11, 1) - Yeear(Shheet1.Cel

23、lss(Z, 44) * 12 + Montth(Sheeet1.CCells(1, 1) - MMonth(Sheett1.Cellls(Z, 4) Endd If If Year(Sheett1.Cellls(1, 1) = Yeear(Shheet1.Cellss(Z, 44) Annd Monnth(Shheet1.Cellss(1, 11) = Montth(Sheeet1.CCells(Z, 4) Andd Day(Sheett1.Cellls(1, 1) = Yeear(Shheet1.Cellss(Z, 44) Annd Monnth(Shheet1.Cellss(1, 11)

24、 = Dayy(Sheeet1.Ceells(ZZ, 4) Thenn m = (Yearr(Sheeet1.Ceells(11, 1) - Yeear(Shheet1.Cellss(Z, 44) * 12 - Montth(Sheeet1.CCells(1, 1) + MMonth(Sheett1.Cellls(Z, 4) Endd If If Year(Sheett1.Cellls(1, 1) = Yeear(Shheet1.Cellss(Z, 44) Annd Monnth(Shheet1.Cellss(1, 11) = Dayy(Sheeet1.Ceells(ZZ, 4) Thenn

25、m = (Yearr(Sheeet1.Ceells(11, 1) - Yeear(Shheet1.Cellss(Z, 44) * 12 - Montth(Sheeet1.CCells(1, 1) + MMonth(Sheett1.Cellls(Z, 4) - 1 Endd If If Year(Sheett1.Cellls(1, 1) Yeaar(Sheeet1.CCells(Z, 4) Theen m = 0 Endd If If Year(Sheett1.Cellls(1, 1) = Yeaar(Sheeet1.CCells(Z, 4) Andd Montth(Sheeet1.CCells

26、(1, 1) MMonth(Sheett1.Cellls(Z, 4) Then m = 0 Endd If If Year(Sheett1.Cellls(1, 1) = Yeaar(Sheeet1.CCells(Z, 4) Andd Montth(Sheeet1.CCells(1, 1) = MMonth(Sheett1.Cellls(Z, 4) And DDay(Shheet1.Cellss(1, 11) Day(SSheet11.Cellls(Z, 4) TThen m = 0 Endd If - zhuuan = Int(m / Sheeet1.CCells(Z, 5) - 11 轉存次

27、次數 If zhuann 22 And Sheett1.Cellls(Z, 6) Then Sheeet1.Ceells(ZZ, 7) = daooqi -到期總數數 If SSheet11.Cellls(Z, 4) TThen Sheett1.Cellls(Z, 8).FFormullaR1C11 = _ =DATTE(YEAAR(RC-4),MONTHH(RC-4)+RRC-3,DAY(RC-44) -到期日期期 End If Endd If If Sheett1.Cellls(Z, 7) Then Sheeet1.Ceells(ZZ, 9) = (Shheet1.Cellss(Z, 77

28、) - SSheet11.Cellls(Z, 2) -利利息 Elsse Sheeet1.Ceells(ZZ, 9) = Endd If Sheeet1.CCells(Z, 100) = zzhuan -轉存存次數 zhuuancunn(1) = Sheeet1.Ceells(ZZ, 7) + Sheeet1.CCells(Z, 7) * (llv / 1100) * (Sheeet1.CCells(Z, 5) / 122) * (1 - llixishhui) 第一次轉存存到期總數 If zhuann = 22 Thenn For i = 22 To zhuann zhuanncun(i)

29、= zhuanncun(ii - 1) + zhhuancuun(i - 1) * (lv / 1100) * (Sheeet1.CCells(Z, 5) / 122) * (1 - llixishhui) Nextt Endd If If Int(m / SSheet11.Cellls(Z, 5) = m / Sheett1.Cellls(Z, 5) TThen Sheeet1.Ceells(ZZ, 11).FormmulaR11C1 = _ =DAATE(YEEAR(RCC-7),MONTTH(RC-7)+RC-66*(RCC-1+1),DAAY(RC-7) -轉存存到期日期 Elsse

30、Sheeet1.Ceells(ZZ, 11).FormmulaR11C1 = _ =DAATE(YEEAR(RCC-7),MONTTH(RC-7)+RC-66*(RCC-1+1),DAAY(RC-7) -轉存存到期日期 Endd If If zhuann 1 Then Sheeet1.Ceells(ZZ, 10) = Sheeet1.Ceells(ZZ, 11) = Sheeet1.Ceells(ZZ, 12) = Shheet1.Cellss(Z, 77) Sheeet1.Ceells(ZZ, 13) = Shheet1.Cellss(Z, 99) Elsse Sheeet1.Ceells(

31、ZZ, 12) = zhhuancuun(zhuann) -當前前總額 Sheeet1.Ceells(ZZ, 13) = Shheet1.Cellss(Z, 112) - Sheett1.Cellls(Z, 2) -當當前利息 Endd If - 格式 If ZZ Mod 2 = 11 Thenn 當前行數除以2的余數為1（奇數） With Rangee(Sheeet1.Ceells(ZZ, 1), Sheeet1.Ceells(ZZ, 14).Intteriorr .Patteern = xlSollid .PatteernCollorInddex = xlAuttomatiic .Colo

32、rr = 1001980115 .TintAAndShaade = 0 .PatteernTinntAndSShade = 0 End WWith ElseeIf Z Mod 22 = 0 Then 當當前行數除以2的余數為0（偶數） With Rangee(Sheeet1.Ceells(ZZ, 1), Sheeet1.Ceells(ZZ, 14).Intteriorr .Patteern = xlSollid .PatteernCollorInddex = xlAuttomatiic .Colorr = 1334218223 .TintAAndShaade = 0 .PatteernTinn

33、tAndSShade = 0 End WWith End If - 未未選擇銀行不不計利率 If Sheett1.Cellls(Z, 1) = TThen lv = 00 Sheeet1.Ceells(ZZ, 6) = Sheeet1.Ceells(ZZ, 7) = Sheeet1.Ceells(ZZ, 9) = Endd If 活期期不考慮到期期時間，按存存入時間到當當前日期利息息計算 If Sheett1.Cellls(Z, 3) = 活期 Thhen tiann = Daate - Sheett1.Cellls(Z, 4) niann1 = IInt(tian / 365) 年數數 yu

34、e11 = Shheet1.Cellss(Z, 55) - 112 * nnian 月數 Sheeet1.Ceells(ZZ, 7) = Sheeet1.CCells(Z, 2) + Shheet1.Cellss(Z, 22) * llv / 1100 * (tiann / 3665) Sheeet1.Ceells(ZZ, 9) = Sheeet1.CCells(Z, 7) - Shheet1.Cellss(Z, 22) Sheeet1.Ceells(ZZ, 5) = Sheeet1.Ceells(ZZ, 6) = lv Sheeet1.Ceells(ZZ, 8) = Sheeet1.Ceel

35、ls(ZZ, 10) = Sheeet1.Ceells(ZZ, 11) = Sheeet1.Ceells(ZZ, 12) = Shheet1.Cellss(Z, 77) Sheeet1.Ceells(ZZ, 13) = Shheet1.Cellss(Z, 99) Endd If 未選選擇存入時間間 If Sheett1.Cellls(Z, 4) = TThen Sheeet1.Ceells(ZZ, 9) = Sheeet1.Ceells(ZZ, 10) = Sheeet1.Ceells(ZZ, 11) = Sheeet1.Ceells(ZZ, 13) = Sheeet1.Ceells(ZZ,

36、7) = Sheeet1.CCells(Z, 2) Sheeet1.Ceells(ZZ, 12) = Shheet1.Cellss(Z, 22) Endd If 到期期提示 If Sheett1.Cellls(Z, 8) Sheeet1.Ceells(11, 1) Then Withh Sheeet1.Ceells(ZZ, 8).Font .Coloor = -652811 .TinttAndShhade = 0 End With Elsse Withh Sheeet1.Ceells(ZZ, 8).Font .ThemmeColoor = xxlThemmeColoorLighht1 .Tin

37、ttAndShhade = 0 End With Endd If If Sheett1.Cellls(Z, 11) Sheeet1.CCells(1, 1) Thenn Withh Sheeet1.Ceells(ZZ, 11).Fontt .Coloor = -652811 .TinttAndShhade = 0 End With Elsse Withh Sheeet1.Ceells(ZZ, 11).Fontt .ThemmeColoor = xxlThemmeColoorLighht1 .TinttAndShhade = 0 End With Endd IfNext-For j = 3 To

38、o rn + 11000 If Sheett1.Cellls(j, 2) = AAnd Shheet1.Cellss(j, 33) Thhen sc = MsgBBox(確確認刪除該項項目？, vbYessNo, 提示) If ssc = vvbYes Then Sheett1.Cellls(j, 1) = Sheett1.Cellls(j, 3) = Sheett1.Cellls(j, 4) = Sheett1.Cellls(j, 5) = Sheett1.Cellls(j, 6) = Sheett1.Cellls(j, 7) = Sheett1.Cellls(j, 8) = Sheett1

39、.Cellls(j, 9) = Sheett1.Cellls(j, 10) = Sheett1.Cellls(j, 11) = Sheett1.Cellls(j, 12) = Sheett1.Cellls(j, 13) = Sheett1.Cellls(j, 14) = End If Rowss(j).DDeletee Endd IfNext圖表數據處理理圖表1，餅圖圖，顯示各銀銀行資金分布布Sheet6.Colummns(AA:B).ClearrSheet6.Cellss(1, 11) = 圖表1zong = 0ben = 00nianli = 0For K = 3 Too rn rn11

40、= Shheet6.Rangee(a655536).End(xlUp).Row If rn1 = 2 Then ActtiveShheet.CChartOObjectts(圖表 1).Actiivate ActtiveChhart.SSetSouurceDaata Soource:=Sheeet6.Raange(A2:A & rnn1 & , B2:B & rn1 & ) Sheeet1.CCells(r, c).Seleect 設置置繪圖區格式 Witth Sheeets(11).ChaartObjjects(圖表 1) .Namme .Lefft .Topp .Heiight = 250

41、.Widdth .Chaart.ChhartTyype Endd WithhEnd IfWith Raange(O3, O4) .HorrizonttalAliignmennt = xlRRight .VerrticallAlignnment = xlCCenterr .WraapTextt = Faalse .Oriientattion = 0 .AdddIndennt = FFalse .InddentLeevel = 0 .ShrrinkTooFit = Falsse .ReaadingOOrder = xlCContexxt .MerrgeCellls = Falsee Endd Wi

42、thh Witth Rannge(OO3).FFont .Collor = -167551104 .TinntAndSShade = 0 Endd Withh Witth Rannge(OO4).FFont .Collor = -33688704 .TinntAndSShade = 0 Endd Withh Rannge(OO3, O4).Font.Size = 9End Subb界面“收支記錄錄”“收支記錄”中中復制代碼如如下：Dim riDim r(00 To 11)Dim r1(0 To 1)Dim r2(0 To 1)Dim r3(0 To 1)Dim r100(0 Too 1)Dim

43、 r111(0 Too 1)Dim r122(0 Too 1)Dim r133(0 Too 1)Dim konngDim konng1Dim konng2Dim konng10Dim konng11Dim konng12Dim flaag_shaanDim flaag_shaan10Privatee Sub Calenndar1_Clickk()ActiveCCell = Caleendar11Calendaar1.Viisiblee = FaalseEnd SubbPrivatee Sub Workssheet_SelecctionCChangee(ByVall Targget Ass Ra

44、ngge)ri = rii + 1 ri定義為鼠鼠標點擊次數數rik = rri Modd 2 rik為0或1rns = RRange(B655536).End(xxlUp).Row 收入最大行行號rnz = RRange(L655536).End(xxlUp).Row 支出最大行行號If rns = rnnz Theen rn = rnssElse rn = rnzzEnd Ifcns = RRange(B2).End(xlToRRight).Coluumn 最大列號cnz = RRange(L2).End(xlToRRight).Coluumn 最大列號格式初始化With Raange(S

45、Sheet22.Cellls(3, 1), SSheet22.Cellls(rn + 110, cnnz).IInteriior .Paatternn = xllNone .TiintAnddShadee = 0 .PaatternnTintAAndShaade = 0End Witth-With Shheet2.Rangee(A:NN).Vaalidattion .Dellete .Addd Typee:=xlVaalidatteInpuutOnlyy, AlerrtStylle:=xlVaalidAllertSttop, OOperattor _ :=xlBeetweenn .Ignnor

46、eBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh 收入項目列列表更新 rn77s = SSheet77.Rangge(A665536).Endd(xlUpp).Roww f = =收支支項目!A22:A & rn7s & Witth Rannge(BB

47、3).VValidaation .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut

48、= TTrue .ShoowErroor = TTrue Endd Withh 支出項目列列表更新 rn77z = SSheet77.Rangge(C665536).Endd(xlUpp).Roww f = =收支支項目!C22:C & rn7z & Witth Rannge(LL3).VValidaation .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .I

49、nCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh 收入入賬戶列表更更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(DD3).VValidaation .Dellet

50、e .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor =

51、TTrue Endd Withh 支出出賬戶列表更更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(nn3).VValidaation .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .In

52、pputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd WithhFor Z = 2 Too rn 收入入項目列表更更新 rn77s = SSheet77.Rangge(A665536).Endd(xlUpp).Roww f = =收支支項目!A22:A & rn7s & Witth Rannge(BB & ZZ + 1).Valiidatioon .Dell

53、ete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor

54、= TTrue Endd Withh 支出出項目列表更更新 rn77z = SSheet77.Rangge(C665536).Endd(xlUpp).Roww f = =收支支項目!C22:C & rn7z & Witth Rannge(LL & ZZ + 1).Valiidatioon .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropd

55、owwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh 收入入賬戶列表更更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(DD & ZZ + 1).Valiidatioon .Dellete .A

56、ddd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTru

57、e Endd Withh 支出出賬戶列表更更新 rn44 = Shheet4.Rangee(e655536).End(xlUp).Row f = =基本本信息!E55:E & rn4 & Witth Rannge(NN & ZZ + 1).Valiidatioon .Dellete .Addd Typee:=xlVaalidatteListt, AlerrtStylle:=xlVaalidAllertSttop, OOperattor:= _ xlBeetweenn, Forrmula11:=f .IgnnoreBllank = Truee .InCCellDrropdowwn = TTrue

58、 .InpputTittle = .ErrrorTittle = .InpputMesssage = .ErrrorMesssage = .IMEEMode = xlIIMEModdeNoCoontroll .ShoowInpuut = TTrue .ShoowErroor = TTrue Endd Withh On Errorr Resuume Neext If Targeet.Collumn = 1 Annd Tarrget.VValue 日日期 Annd Tarrget.RRow 2 Andd Targget.Roow = rn + 11 Thenn Caleendar11.Visiib

59、le = Truee 日歷控件 Caleendar11.Leftt = Ceells(TTargett.Row, 1).LLeft Caleendar11.Top = Cellls(Taarget.Row + 1, 11).Topp Withh Caleendar11 當前前日期 .Yeaar = YYear(NNow) .Monnth = Monthh(Now) .Dayy = Daay(Noww) End With ElsseIf Targeet.Collumn = 11 AAnd Taarget.Valuee 日期 Annd Tarrget.RRow 2 Andd Targget.Roo

60、w = rn + 11 Thenn Caleendar11.Visiible = Truee 日歷控件 Caleendar11.Leftt = Ceells(TTargett.Row, 11).Left Caleendar11.Top = Cellls(Taarget.Row + 1, 111).Toop Withh Caleendar11 當前前日期 .Yeaar = YYear(NNow) .Monnth = Monthh(Now) .Dayy = Daay(Noww) End With Elsse Caleendar11.Visiible = Falsse Endd If 格式 If Z