Excel理財(cái)收支管理代碼

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

2、差別，存存不同銀行收收益相差多少少也能方便了了解。界面“銀行記錄錄”“銀行記錄”中中復(fù)制代碼如如下：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 最最大行號(hào)cn = Raange(b2).

4、End(xxlToRiight).Colummn 最最大列號(hào)Applicaation.ScreeenUpdaating = Fallse數(shù)據(jù)初始化If Sheeet1.Ceells(AActiveeCell.Row, 1) = Annd Sheeet1.CCells(ActivveCelll.Row, 3) = AAnd Shheet1.Cellss(ActiiveCelll.Roww, 2) Thenn Sheeet1.CCells(ActivveCelll.Row, 1) = 中國(guó)銀銀行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 顏顏色暫不設(shè)置置 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“銀行項(xiàng)目目” 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項(xiàng)目列表 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 項(xiàng)目目列表 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(未區(qū)區(qū)分各家銀

20、行行計(jì)算方法，結(jié)結(jié)果不一定準(zhǔn)準(zhǔn)確，按利率率60%計(jì)算？, 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 轉(zhuǎn)存次

27、次數(shù) If zhuann 22 And Sheett1.Cellls(Z, 6) Then Sheeet1.Ceells(ZZ, 7) = daooqi -到期總數(shù)數(shù) 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 -轉(zhuǎn)存存次數(shù) zhuuancunn(1) = Sheeet1.Ceells(ZZ, 7) + Sheeet1.CCells(Z, 7) * (llv / 1100) * (Sheeet1.CCells(Z, 5) / 122) * (1 - llixishhui) 第一次轉(zhuǎn)存存到期總數(shù) 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) -轉(zhuǎn)存存到期日期 Elsse

30、Sheeet1.Ceells(ZZ, 11).FormmulaR11C1 = _ =DAATE(YEEAR(RCC-7),MONTTH(RC-7)+RC-66*(RCC-1+1),DAAY(RC-7) -轉(zhuǎn)存存到期日期 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) -當(dāng)前前總額 Sheeet1.Ceells(ZZ, 13) = Shheet1.Cellss(Z, 112) - Sheett1.Cellls(Z, 2) -當(dāng)當(dāng)前利息 Endd If - 格式 If ZZ Mod 2 = 11 Thenn 當(dāng)前行數(shù)除以2的余數(shù)為1（奇數(shù)） 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 當(dāng)當(dāng)前行數(shù)除以2的余數(shù)為0（偶數(shù)） 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 - 未未選擇銀行不不計(jì)利率 If Sheett1.Cellls(Z, 1) = TThen lv = 00 Sheeet1.Ceells(ZZ, 6) = Sheeet1.Ceells(ZZ, 7) = Sheeet1.Ceells(ZZ, 9) = Endd If 活期期不考慮到期期時(shí)間，按存存入時(shí)間到當(dāng)當(dāng)前日期利息息計(jì)算 If Sheett1.Cellls(Z, 3) = 活期 Thhen tiann = Daate - Sheett1.Cellls(Z, 4) niann1 = IInt(tian / 365) 年數(shù)數(shù) yu

34、e11 = Shheet1.Cellss(Z, 55) - 112 * nnian 月數(shù) 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 未選選擇存入時(shí)間間 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(確確認(rèn)刪除該項(xiàng)項(xiàng)目？, 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圖表數(shù)據(jù)處理理圖表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 設(shè)置置繪圖區(qū)格式 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界面“收支記錄錄”“收支記錄”中中復(fù)制代碼如如下：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定義為鼠鼠標(biāo)點(diǎn)擊次數(shù)數(shù)rik = rri Modd 2 rik為0或1rns = RRange(B655536).End(xxlUp).Row 收入最大行行號(hào)rnz = RRange(L655536).End(xxlUp).Row 支出最大行行號(hào)If rns = rnnz Theen rn = rnssElse rn = rnzzEnd Ifcns = RRange(B2).End(xlToRRight).Coluumn 最大列號(hào)cnz = RRange(L2).End(xlToRRight).Coluumn 最大列號(hào)格式初始化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 收入項(xiàng)目列列表更新 rn77s = SSheet77.Rangge(A665536).Endd(xlUpp).Roww f = =收支支項(xiàng)目!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 支出項(xiàng)目列列表更新 rn77z = SSheet77.Rangge(C665536).Endd(xlUpp).Roww f = =收支支項(xiàng)目!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 收入入項(xiàng)目列表更更新 rn77s = SSheet77.Rangge(A665536).Endd(xlUpp).Roww f = =收支支項(xiàng)目!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 支出出項(xiàng)目列表更更新 rn77z = SSheet77.Rangge(C665536).Endd(xlUpp).Roww f = =收支支項(xiàng)目!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 當(dāng)前前日期 .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 當(dāng)前前日期 .Yeaar = YYear(NNow) .Monnth = Monthh(Now) .Dayy = Daay(Noww) End With Elsse Caleendar11.Visiible = Falsse Endd If 格式 If Z