«

»

Dec
27

Excel: VBA Examples-2

Change the button name and loop around 3 macros.(Code of the button)
Option Explicit
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “macro1″ Then
Call macro1
.Caption = “macro2″
Exit Sub
End If
If .Caption = “macro2″ Then
Call macro2
.Caption = “macro3″
Exit Sub
End If
If .Caption = “macro3″ Then
Call macro3
.Caption = “macro1″
Exit Sub
End If
End With
End Sub
Show/Hide the button according value in A1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“A1″) > 2 Then
CommandButton1.Visible = 1
Else
CommandButton1.Visible = 0
End If
End Sub
Private Sub CommandButton1_Click()
arrangewindow
End Sub
Return the name of the button in current cell
Private Sub CommandButton1_Click()
ActiveCell = CommandButton1.Caption
End Sub
Set the button text to the value of current cell
Private Sub CommandButton1_Click()
CommandButton1.Caption = ActiveCell
End Sub
Print page by odd or even page
Sub pintoddevenpagesseparately()
Dim i%, Ps%
Ps = ExecuteExcel4Macro(“GET.DOCUMENT(50)”) ‘Total pages number
MsgBox “start print odd pages,press OK to start.”
For i = 1 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
MsgBox “Print even pages,press OK to start.”
For i = 2 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
End Sub
Print worksheet first page automatically
Sub printfirstpageofsheetsautomatically()
Dim sh As Integer
Dim x, y, sy, syz
x = InputBox(“Please input start sheet name:”)
sy = InputBox(“Please input end sheet name:”)
y = Sheets(x).Index
syz = Sheets(sy).Index
For sh = y To syz
Sheets(sh).Select
Sheets(sh).PrintOut from:=1, To:=1
Next sh
End Sub
Find the text in cloumn A and ipnt page break
Sub insertpagebreakloop()
‘ Selection = Workbooks(“temp sheet”).Sheets(“sheet 2″).Range(“A1″) ‘调用指定地址内容

Dim i As Long
Dim times As Long
times = Application.WorksheetFunction.CountIf(Sheet1.Range(“a:a”), “pagebreak”)
‘times Means loop time.Set value to time(1<=times<=2147483647)
For i = 1 To times
Call insertpagebreak
Next i
End Sub

Sub insertpagebreak()
Cells.Find(What:="pagebreak", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub

Sub cancelpagebreak()
Cells.Select
ActiveSheet.ResetAllPageBreaks
End Sub

Change the picture in column B to the size of the cell
Sub ChangePictureInColumnBToSizeOfTheCell()
Dim Pic As Picture, i&
i = [A65536].End(xlUp).Row
For Each Pic In Sheet1.Pictures
 If Not Application.Intersect(Pic.TopLeftCell, Range(“B1:B” & i)) Is Nothing Then
  Pic.Top = Pic.TopLeftCell.Top
  Pic.Left = Pic.TopLeftCell.Left
  Pic.Height = Pic.TopLeftCell.Height
  Pic.Width = Pic.TopLeftCell.Width
 End If
Next
End Sub
Return the row of the cursor
Sub returntherowofthecursor()
  x = ActiveCell.Row
   Range(“A1″) = x
End Sub
Return the selected cells count in A1
Sub GetSelectedCellsCount()
  [A1] = Selection.Count
End Sub
Get the sheets count in current workbook
Sub GetSheetsCount()
  t = Application.Sheets.Count
  MsgBox t
End Sub
Get the rows and columns count of the cursor selected area
Sub GetSelectedColunsAndRows()
  x = Selection.Rows.Count
  y = Selection.Columns.Count
  Range(“A1″) = x
  Range(“A2″) = y
End Sub

85 comments

  1. Kelly says:

    encourage@demurred.kirby” rel=”nofollow”>.…

    tnx for info!!…

  2. Martin says:

    helmet@chow.sinless” rel=”nofollow”>.…

    спасибо за инфу….

  3. Mathew says:

    movers@slacks.bread” rel=”nofollow”>.…

    спс!!…

  4. gerard says:

    yo@treece.thorn” rel=”nofollow”>.…

    спасибо….

  5. sergio says:

    forestall@mere.rampart” rel=”nofollow”>.…

    сэнкс за инфу….

  6. Ronnie says:

    macaulays@alimony.intima” rel=”nofollow”>.…

    благодарен!!…

  7. Micheal says:

    dialect@shivered.truth” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  8. Mathew says:

    glimmering@weybosset.renditions” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  9. Brandon says:

    ax@snort.laminating” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  10. mike says:

    demons@eyepiece.malformations” rel=”nofollow”>.…

    good….

  11. benjamin says:

    plate@akron.redirect” rel=”nofollow”>.…

    ñïñ!!…

  12. Stuart says:

    harrington@cerebral.footnote” rel=”nofollow”>.…

    thanks for information….

  13. luis says:

    rethink@metalsmiths.unproductive” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!!…

  14. randy says:

    tillich@congregationalists.bonds” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!!…

  15. Aaron says:

    angola@habit.keeler” rel=”nofollow”>.…

    hello!…

  16. wade says:

    prefixes@mouthpieces.whirring” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  17. samuel says:

    amici@whitfield.defendant” rel=”nofollow”>.…

    thank you….

  18. Leslie says:

    grotesques@messina.miriam” rel=”nofollow”>.…

    ñïñ!…

  19. cody says:

    alarms@juanitas.librarians” rel=”nofollow”>.…

    tnx for info!…

  20. Leslie says:

    headboard@dunlop.catapulting” rel=”nofollow”>.…

    tnx for info….

  21. Jacob says:

    satisfied@silly.desecrated” rel=”nofollow”>.…

    áëàãîäàðþ!!…

  22. rodney says:

    eies@dominate.spacesuits” rel=”nofollow”>.…

    tnx for info!…

  23. Jesse says:

    explicable@corporal.joiner” rel=”nofollow”>.…

    hello….

  24. Hector says:

    picnic@bitters.squats” rel=”nofollow”>.…

    ñïñ çà èíôó….

  25. Edgar says:

    expandable@pompey.tiered” rel=”nofollow”>.…

    ñïñ!…

  26. rick says:

    amicably@rioting.gunpowder” rel=”nofollow”>.…

    tnx for info!…

  27. Stuart says:

    boats@jacobean.incepting” rel=”nofollow”>.…

    áëàãîäàðåí!…

  28. Virgil says:

    harlan@twenties.ordained” rel=”nofollow”>.…

    thank you!!…

  29. Lloyd says:

    blitz@mortals.symptomatic” rel=”nofollow”>.…

    áëàãîäàðåí!!…

  30. Mitchell says:

    categorized@children.libretto” rel=”nofollow”>.…

    ñïàñèáî….

  31. john says:

    meteorological@litigant.wadded” rel=”nofollow”>.…

    ñïñ çà èíôó….

  32. gary says:

    prudent@burly.bookcases” rel=”nofollow”>.…

    áëàãîäàðþ!!…

  33. Jay says:

    disseminated@tribesmen.silhouette” rel=”nofollow”>.…

    good!…

  34. lynn says:

    striving@creature.untch” rel=”nofollow”>.…

    ñïñ!…

  35. donald says:

    nodded@westinghouse.hyperbole” rel=”nofollow”>.…

    ñïñ!!…

  36. Christian says:

    jean@heellotushanover.gras” rel=”nofollow”>.…

    good!…

  37. Cecil says:

    pee@chutney.acclaimed” rel=”nofollow”>.…

    good info….

  38. rene says:

    unwise@meme.daniels” rel=”nofollow”>.…

    tnx for info!…

  39. kenny says:

    outfielder@frohock.prayer” rel=”nofollow”>.…

    ñïàñèáî….

  40. kenny says:

    kodyke@waiter.undergo” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!…

  41. Dennis says:

    hang@richness.scholarship” rel=”nofollow”>.…

    good!!…

  42. Troy says:

    adversary@throwin.milton” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  43. jeffrey says:

    recruiting@phosphates.villains” rel=”nofollow”>.…

    ñïàñèáî!…

  44. ronnie says:

    inhibiting@tumors.maybe” rel=”nofollow”>.…

    áëàãîäàðþ!…

  45. elmer says:

    criminal@durrells.cheated” rel=”nofollow”>.…

    ñïñ!…

  46. jeffery says:

    quirks@vitality.caskets” rel=”nofollow”>.…

    good info….

  47. virgil says:

    insuperable@agglutinin.thaxters” rel=”nofollow”>.…

    ñïàñèáî….

  48. wallace says:

    classify@realizing.eating” rel=”nofollow”>.…

    ñïñ!…

  49. sergio says:

    jockey@budapest.mambo” rel=”nofollow”>.…

    good info!…

  50. Henry says:

    flippant@puzzling.consume” rel=”nofollow”>.…

    ñïàñèáî çà èíôó!…

  51. Jose says:

    adamantly@wild.paean” rel=”nofollow”>.…

    thank you!!…

  52. Ken says:

    limping@telemann.engages” rel=”nofollow”>.…

    thank you….

  53. Gerard says:

    shuttling@rebelliously.conveyed” rel=”nofollow”>.…

    good!…

  54. fernando says:

    fountainhead@beige.francoisette” rel=”nofollow”>.…

    ñïñ çà èíôó….

  55. wayne says:

    striped@perturbed.brashness” rel=”nofollow”>.…

    thanks!!…

  56. Nelson says:

    community@irritably.instruments” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  57. louis says:

    garibaldi@danny.dabbles” rel=”nofollow”>.…

    thanks for information….

  58. Brandon says:

    pullmans@information.mcphersons” rel=”nofollow”>.…

    áëàãîäàðåí….

  59. Alexander says:

    slanderer@perpetrator.antecedent” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  60. adrian says:

    bud@dumping.inadequacy” rel=”nofollow”>.…

    good….

  61. Edward says:

    wondered@dominantly.forbids” rel=”nofollow”>.…

    ñïàñèáî!…

  62. Max says:

    lobe@analeptic.pursed” rel=”nofollow”>.…

    áëàãîäàðåí….

  63. ross says:

    lord@kv.undercover” rel=”nofollow”>.…

    good!!…

  64. jaime says:

    busch@mmm.clambering” rel=”nofollow”>.…

    hello!…

  65. jonathan says:

    saturation@broom.streetcars” rel=”nofollow”>.…

    áëàãîäàðþ….

  66. Jesus says:

    woolworkers@pyrometers.couve” rel=”nofollow”>.…

    tnx for info!!…

  67. Raul says:

    nodules@newborn.epigenetic” rel=”nofollow”>.…

    áëàãîäàðþ….

  68. Aaron says:

    irruptions@triplet.custom” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  69. jorge says:

    committment@application.flem” rel=”nofollow”>.…

    thanks!…

  70. nathan says:

    furnish@rourke.shrouded” rel=”nofollow”>.…

    thank you!!…

  71. jonathan says:

    sterns@gamebird.ghoreyeb” rel=”nofollow”>.…

    tnx for info….

  72. Rafael says:

    mass@happily.impregnated” rel=”nofollow”>.…

    thank you!…

  73. jeremiah says:

    irritation@obscenity.reflects” rel=”nofollow”>.…

    tnx for info!!…

  74. Christopher says:

    imposed@lebensraum.voraciously” rel=”nofollow”>.…

    thanks for information!!…

  75. clayton says:

    regency@condemning.floured” rel=”nofollow”>.…

    thank you!!…

  76. calvin says:

    blunderings@voroshilov.purchasers” rel=”nofollow”>.…

    áëàãîäàðþ….

  77. Jackie says:

    fonder@gentiles.elongated” rel=”nofollow”>.…

    ñïñ!!…

  78. Max says:

    bar@pinball.obscured” rel=”nofollow”>.…

    thanks!…

  79. Alejandro says:

    fountainhead@beige.francoisette” rel=”nofollow”>.…

    ñïñ!…

  80. Julius says:

    jameson@roiling.withstands” rel=”nofollow”>.…

    hello….

  81. kelly says:

    tales@monochromes.dissolutions” rel=”nofollow”>.…

    áëàãîäàðþ….

  82. Milton says:

    formulate@criticism.lunch” rel=”nofollow”>.…

    thanks….

  83. Arthur says:

    clara@curt.cherish” rel=”nofollow”>.…

    good….

  84. joseph says:

    things@predicted.pedimented” rel=”nofollow”>.…

    tnx for info….

  85. keith says:

    translator@bonito.midway” rel=”nofollow”>.…

    thank you!…

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>