›
›

Những kỹ năng tùy biến bảng tính Excel

pdf 117 trang phuongnguyen 2950
Download
Bạn đang xem 20 trang mẫu của tài liệu "Những kỹ năng tùy biến bảng tính Excel", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfnhung_ky_nang_tuy_bien_bang_tinh_excel.pdf

Nội dung text: Những kỹ năng tùy biến bảng tính Excel

  1. Y ueeu • uuu • uueeu • uueu • ue • uuuuơ uuu a aư uư euơư eưeu uơ ueeư eưu a aư a uưuưu ơ aeu uuưe u ưeư uu ơ uư ưuư uuưu ưưuư ư e ư ơuưeeu u uueeuu uue a uu ưuuuue ưuuưu ưeuuuu uưư ee u ưưeueuuue
  2. uuuuơ aa ưeee uu ueue eeeư ư eeaee e ➝ Ae ư a Ae ưưu a eeA ee ae ueue ưeu a ➝e a ueuu u uu euuu uu ơư u eaa u ưuưu
  3. ae eưe ưue uưuưưuu eu e e ➝a eưeu ➝ ➝aa aa e e uu uu
  4. ưưưuuư ư euư eơ ư ơu ưuưuư uuư uưu uu ưuuu ưuuuu ưuu euuuư u ee uu euu ae ư euưu ưe e aa euơ ơeuu u ee e ee eAaeA Aae ueưuu
  5. uueưu uuuưu uueưuưu
  6. uueưuưu ưuu uưueưư u
  7. e aeae eaeae eeeơưue uuu eue eu ư ưưưu eưeưue uuuu uuuu uuu uuu ee ưưeuuueeu ueeuư ơu ưuuuuee uưưeeu eea eeeeơu eeuu eeuee e ueeu eeueeuuee ee ueeuuuuu uee
  8. ưưưưuu uuuu ueeuu ươưuuuu ueeư uuuee uưeeuuuu ee ueơe ưưeeueee ueeee eeueeư ueeeee ưưưeeeueeư ưuuee euưeeeee ueeue ưeuueee ueu e Private Sub Worksheet_SelectionChange (ByVal Target As Range ) If Not Intersect (Range ("MyRange" ), Target ) Is Nothing Then Sheets (Array( "Sheet5" , "Sheet3" , "Sheet1" )). Select Else Me .Select End If End Sub eeeưuueeu eeuưeeue eeee eưu
  9. eeeưuee u eeee eueueeu ueeuuuơee euuu u uuuưuưeeưu e e Private Sub worksheet_Change (ByVal Target As Range ) If Not Intersect (Range ("MyRange" ), Target ) Is Nothing Then With Range ("MyRange" ) .Copy Destination := Sheets ("Sheet3" ). Range ("A1" ) .Copy Destination := Sheets ("Sheet1" ). Range ("D10" ) End With End If End Sub eưuee eeue eeuue eeeeeeeư ư e eee e ươ ưue uu uươ
  10. eeee ưeưueee uư uưưuu u e ➝e ➝ee ➝ee e ➝ae eu u aeaa a eu eu ươu aeaa a e ư ư ue e ➝ae eea eeee eaeee ưưu ưuưuơ uuu ưaư ơ uưưu ưuuuưu u ư eee eưu ưưưuưuư eưu ưuưu ưeuưưưuu ưeu eee ➝aa A u u ee u eee e ➝ a u eeeae u uưeeueeueeư ư ueeuee
  11. euu A eưu e Private Sub workbook_BeforeSave (ByVal SaveAsUI As Boolean , _ Cancel As Boolean ) Dim lReply As Long If SaveAsUI = True Then lReply = MsgBox ("Sorry, you are not allowed to save this workbook as another name." _ & "Do you wish to save this workbook?" , vbQuestion + vbOKCancel ) Cancel = (lReply = vbCancel ) If Cancel = False Then Me .Save Cancel = True End If End Sub ae eưuư aea ưuư u ưueeư ưueee ưuư uuuuư ươ
  12. uuuư ee eu e Private Sub workbook_BeforePrint (Cancel As Boolean ) Cancel = True MsgBox "Sorry, you cannot Print from this workbook" , vbInformation End Sub ueưuuu eư ưưưươ e uueeee eu e Private Sub workbook_BeforePrint (Cancel As Boolean ) Select Case ActiveSheet .Name Case "Sheet1" , "Sheet2" Cancel = True MsgBox "Sorry, you cannot print this sheet from this workbook" , _ vbInformation End Select End Sub ee e eeeeuee uưuee eeu e u ee e e uư ueeuuee eu e Private Sub Workbook_NewSheet (ByVal Sh As Object ) Application .DisplayAlerts = False MsgBox "Sorry, you cannot add any more sheets to this workbook" , _ vbInformation Sh .Delete Application .DisplayAlerts = True End Sub eeưeu eeeee ư e e eee ee ee e ae eea eeuuueu uưuee
  13. ooaoa ooo ooaooao oaaaooa aa oaoooooaao ooooaoooooaao • aao aaa aa • ooaoaoaaaooao • aoaaaooaoo o • aoaaaoooaoaoa aoooaao aaooaaaaao aaaooaoo aaooaooa aa oo a aa o aooooo aoo
  14. aooao ooooơ oao a a o aooaaooooao oaoaoơoaooaao oooa oaaoooooaoooaoo aooaaoooooaoao oaoaoooao oaooaoa o ooao aooaao ao aoo oa aoaooaaooooa oaaoooaa ooaoooooaoaaooooa o Private Sub Workbook_BeforeClose (Cancel As Boolean ) Me .Saved = True End Sub oaaoooooooo oaooaa ooooaoaooooa oa
  15. oaoa oaaoo o Private Sub Workbook_BeforeClose (Cancel As Boolean ) Me .Save End Sub ooaaoooo aa oaaoooo aoaoaooa ooa a oo aa o aoaooooaoao aoa aoaa ao aoa aoaaa o Sub MyMacro ( ) ' ' MyMacro Macro Application .DisplayAlerts = False ActiveSheet .Delete Application .DisplayAlerts = True End Sub oaaao aooaao oo eeaưea eeuưưu ueeuu ưeeu uee e ➝ee ➝ee ➝eư ơuưe ➝e
  16. e ➝ee ➝e oơa oaoooao aooo ➝aa ooaooooo ➝ao ➝aaoooo oooao ooooo aaa aaao aoa o
  17. eae ee e e ee uư ưeeu
  18. ưưeee uươu ưu uưưuuu ưuưu ươu u ưeee ươưee ưuuưu uu eee uueu ee aee e
  19. ee e uưuee ưueư ưưuee eeưu u aea eeae
  20. eeeee ae eeưuu ưeeưe eưưuee uưueeư e ➝e ee eae e
  21. eeuưu uưuưu eưưuee
  22. aaeae ư eae e e ư eeuu uuu ư ưueeưeư ưu eaeee eeeeee uuu uưuơuu ee ae e e u
  23. uưueeư u ueee ee e uơưuee ưư eae e e eeư ưueeuưu eeu ư ee uuuuee ư uuư uư uươu ơ e eeưeu eeue eeươ eeeu ee e e ee
  24. ee aee ee u ươuueeee ưuuu A u eeư eeưuư ưeueeeee e ee ee ơ A ue e Private Sub Worksheet_Activate () Dim wSheet As Worksheet Dim lCount As Long lCount = 1 With Me .Columns (1). ClearContents .Cells (1, 1) = "INDEX" End With For Each wSheet In Worksheets If wSheet .Name <> Me .Name Then lCount = lCount + 1 With wSheet .Range ("A1" ). Name = "Start" & wSheet .Index .Hyperlinks .Add Anchor :=. Range ("A1" ), Address := "" , SubAddress := _ "Index" , TextToDisplay := "Back to Index" End With Me .Hyperlinks .Add Anchor := Me .Cells (lCount , 1), Address := "" , SubAddress := _ "Start" & wSheet .Index , TextToDisplay := wSheet .Name End If Next wSheet End Sub A uưu ueeueee eee ưueeeeee eeuu uueeư ơ
  25. ưuueeuơeư ưe ueeưueee uee eeaeeeeeeAae eeeeea ee aa ơ ươee uu ưu
  26. oơ aa aaa e Private Sub Workbook_SheetBeforeRightClick (ByVal Sh As Object , ByVal Target As Range , Can cel As Boolean ) Dim cCont As CommandBarButton On Error Resume Next Application .CommandBars ("Cell" ). Controls ("Sheet Index" ). Delete On Error GoTo 0 Set cCont = Application .CommandBars ("Cell" ). Controls .Add _ (Type := msoControlButton , Temporary := True ) With cCont .Caption = "Sheet Index" .OnAction = "IndexCode" End With End Sub oo ooa e Sub IndexCode () Application .CommandBars ("workbook Tabs" ). ShowPopup End Sub
  27. eeeơ ueeư A eu eeơeeeuư ee a uuuuuuu uueư eeư ưưư ưeu uuưư e ơueuu eu u eeuuư uuuuee eeeeưu e ươưuu eu uưuuuuư
  28. e eeeu ưu ueeeeeee eeeeuuee u uuuuu uư ueưuưuueee eueeeee ueee Private Sub Worksheet_Activate ( ) Me .ScrollArea = "A1:H50" End Sub ưu uưưu ư
  29. euuee uuư ee ue eeee ue eeee Sub MyMacro ( ) ActiveSheet .ScrollArea = "" Range ("Z100" ). Select Selection .Font .Bold = True ActiveSheet .ScrollArea = "$A$1:$G$50" Sheets ("Daily Budget" ). Select ActiveSheet .ScrollArea = "" Range ("T500" ). Select Selection .Font .Bold = False ActiveSheet .ScrollArea = "$A$1:$H$25" End Sub au uau uu u ươơuu eu Private Sub Worksheet_Activate ( ) Me .ScrollArea =Me .UsedRange . Address End Sub u aau Private Sub Worksheet_Activate () With Me .UsedRange Me .ScrollArea = .Resize (. Rows .Count + 5, .Columns .Count + 2). Address End With End Sub uuơau uu
  30. Sub ResetScrollArea ( ) ActiveSheet .ScrollArea = "" End Sub aa uuuưuu eeueeueeee aa ư ưu eue uưu u u aa euưeu eeư ư u u ae e u e
  31. u e ➝ee ➝ea e ea u a u ưuư u
  32. ae ưu e u uu e ue e a e eee ee eee e eee eeeu eeee eeee u
  33. ưeưu e aaaa ơ uươ ư uuưuư ơuươ ea ư aaaa eu aa e u u A u a ưu u uươ ư ươeư eư uuư ưươu A eeuue u e Private Sub Worksheet_SelectionChange (ByVal Target As Range ) If Target .Locked = True Then Me .Protect Password := "Secret" Else
  34. Me .Unprotect Password := "Secret" End If End Sub u ee uu ee u uưuee e ➝Aeee ee ee uươư e ưeue ưưe uuư eưe ư e eueưư ưu eueưư eư ưưuu uơ eue ưu uưuuu eu a aa eue
  35. aa a u a uu u ae u euuuu euuươ euưe ueuưu
  36. eueu ưưu u e aa ee aeae e aeae u ae Ue u u
  37. aa ueu ueưưuuuu ơ uuuuơ ưuue ưuuuu u eee eueeeueeee ueeeuuee u uuuuơ
  38. eueeuu
  39. eueeuu uu uuuuuuơ ueuu ueue a uuư euưu ư u u e eư u uueu ueuư ư eơeue ưu e A u u e uuưe eu e ee ư uuưeeue
  40. eu e Option Explicit Private Sub Workbook_Activate () On Error Resume Next With Application .CommandBars ("MyCustomToolbar" ) .Enabled = True .Visible = True End With On Error GoTo 0 End Sub Private Sub Workbook_Deactivate () On Error Resume Next Application .CommandBars ("MyCustomToolbar" ). Enabled = False On Error GoTo 0 End Sub u u A e e u uuu u u u u ưuưee u ee ưeee euuee u u ee eeư ee eu e Option Explicit Private Sub Worksheet_Deactivate () On Error Resume Next Application .CommandBars ("MyCustomToolbar" ). Enabled = False On Error GoTo 0 End Sub Private Sub Worksheet_Activate () On Error Resume Next With Application .CommandBars ("MyCustomToolbar" ) .Enabled = True .Visible = True
  41. End With On Error GoTo 0 End Sub A ueu U ueeeu ee u uưe aaươưaa euươu uươưue uuu uuu uuuuu uuu ưuuươuuư uuuu eue uee ưu eeeeeee u eeuuơ ee uư uu uưuưuuuee uuưuu euươuươu uư uơ a eeu eưư
  42. uee ưưư uu ueu u ơ u e eeea ưư eeueưu ư uư aeaae eeu eeuu ơeu eeu eơee ưu u u ue ueu ưe uưe ueư euu uueu ư ưeưe ưưư eeeea eeư eueư ưưue uơeeuu e ➝e ➝ee ➝eue eeueeuu u e eue ưư
  43. ư uư eeeưeuue eưưuư eueu ưu ưuue ưuưưe ưaa e uưeeee eeưuuueee ueeeeeưeeueee ee eeuuee ueeuưuư uưueue ưueue uu ưeeư auu uau u auaa uauu u uuuu auuau uua auuu uuau u uuuau auaauơ uaau uauuuuau aaau auau a uauau uuaaa uuu uauuu uaua ơuauu uaaua
  44. auu u ➝a ➝ ➝u ➝a ➝ ➝a aaa aua auuuu auua u auuauau uaa aa uau a uuu uauaaua uuaaauu uaaauuu aơauu aaaua aauaaaauaa aau
  45. au auuu uu auauu a auauaa uauua u ua au
  46. ơ uue ưuưưu u aưaơ ư uưưưu uuư ư ư ưưuưu uuuưưuuuư uơ eưu uuuưe uưưu ưeeeeeeư ưuư ưuu
  47. • ae e euưưuưưu ưe e • aưe ee ư eư e ưuu ư euueưu uư u ưeưe ưuueeeue eưưuuưueeu eưee ưưuuưuuơee uuư ưuuưưu eeeeeưưuuuee ee ưuư
  48. ư uưưư euưưưu eư ueeueeeee ưuu ưuuươeeuu ưeeưeeư eeeeư ueue ee eueee ưuee ee uee ưuưuưue eeươee uueeeuuư ươư ueee ươ e eee ae uưuuư uuưueưu aaaaaee uuee e ae ưue uue uu e eeuueu aa aa aaaa A u e e e ee
  49. uue ưuuuee uơuu u ee ưưư e ee e ư ueeuee ơeeu ee ee ưu ee u uơuuuơưu
  50. ơuưưueeue uuuơuueeư eeưee u Ưaa u eeư ee uu ee uuu ue eu aae ueưu euueư euơ euu uưuuơu eee uueeư eeeeueeeee eeuee ee
  51. eee eeeeưue euưu eeư ueueưue euue eeueueueeee
  52. u ue eeeeeu
  53. uu ueuư ưeuuưư e ươưeeuưe eeuu ư uueuuưu
  54. ư euu ueu uueue ưueư ueuuu u
  55. uưeeươưưư euơ aaa uuuơuuu ue uuee uuu ưu eư eu eueeeư uuuơe ueue euu e Function IsFormula (CheckCells As Range ) IsFormula = CheckCells .HasFormula End Function euuuee uuueuu eu uuư u ưuư uuuu uưuưuu uu uu eu uưu u ư ưưuu ưưueưưưu
  56. ư u ưuuu ưuươ uuu uư uư aưu ư uưu uuuu e ue ưưu e e uuư uue ueơuư uuu uu uuơe e u uu ueưu uư u u uưư ươuư ue uư ưư uuu ưeu u a ưưe uu
  57. u ơơue uuee uưưuu ue u u uu uuu ue uuuu ư ơu ưư uuư a ưe uơ ơuuuu u u ươưư ươ eươu ưươ uu u ư uu uu e euuu u uưư uuư ưu u uu u uưu u uuư