VBA/Excel/Access/Word/Excel/ActiveWorksheet

Adds two hyperlinks to active sheet

   <source lang="vb">

Public Sub AddHyperlinks()

   ActiveSheet.Hyperlinks.Add _
       Anchor:=Range("A1"), _
       Address:="", _
       SubAddress:=""Sheet1"!A1", _
       ScreenTip:=" Goes to Sheet1", _
       TextToDisplay:=" Link to Sheet1"
   ActiveSheet.Hyperlinks.Add _
       Anchor:=Range("A3"), _
       Address:=" http://www.microsoft.ru/ms.htm", _
       ScreenTip:=" Microsoft", _
       TextToDisplay:=" Microsoft web site"

End Sub

</source>
   
  

All properties of PageSetup

   <source lang="vb">

Sub Macro1()

   With ActiveSheet.PageSetup
       .PrintTitleRows = ""
       .PrintTitleColumns = ""
   End With
   ActiveSheet.PageSetup.PrintArea = ""
   With ActiveSheet.PageSetup
       .LeftHeader = ""
       .CenterHeader = "&A"
       .RightHeader = ""
       .LeftFooter = ""
       .CenterFooter = "Page &P"
       .RightFooter = ""
       .LeftMargin = Application.InchesToPoints(0.75)
       .RightMargin = Application.InchesToPoints(0.75)
       .TopMargin = Application.InchesToPoints(1)
       .BottomMargin = Application.InchesToPoints(1)
       .HeaderMargin = Application.InchesToPoints(0.5)
       .FooterMargin = Application.InchesToPoints(0.5)
       .PrintHeadings = False
       .PrintGridlines = True
       .PrintNotes = False
       .CenterHorizontally = False
       .CenterVertically = False
       .Orientation = xlLandscape
       .Draft = False
       .PaperSize = xlPaperLetter
       .FirstPageNumber = xlAutomatic
       .Order = xlDownThenOver
       .BlackAndWhite = False
       .Zoom = 100
   End With

End Sub

</source>
   
  

Change the name of the active worksheet

   <source lang="vb">

Sub changeName()

   ActiveSheet.name = "My Sheet"

End Sub

</source>
   
  

Changing Boolean settings

   <source lang="vb">

Sub Main()

   ActiveSheet.DisplayPageBreaks = False
   ActiveSheet.DisplayPageBreaks = True

End Sub

</source>
   
  

Defined a print area

   <source lang="vb">

Sub printArea()

    ActiveSheet.PageSetup.printArea = ""
    ActiveSheet.Names("Print_Area").delete

End Sub

</source>
   
  

Inserts the sheet names into the SheetNames array

   <source lang="vb">

Sub SortSheets()

   Dim SheetNames()
   SheetCount = ActiveWorkbook.Sheets.Count
   ReDim SheetNames(1 To SheetCount)
   For I = 1 To SheetCount
       SheetNames(I) = ActiveWorkbook.Sheets(I).name
       Debug.Print SheetNames(I)
   Next I

End Sub

</source>
   
  

Macro for changing page setup to landscape orientation

   <source lang="vb">

Sub Macro2()

   With ActiveSheet.PageSetup
       .Orientation = xlLandscape
   End With

End Sub

</source>
   
  

Protecting a Worksheet

   <source lang="vb">

Sub protect()

   ActiveWorksheet.Protect Password:="pass"
</source>
   
  

Protects the myWorksheet worksheet with the same password but allows the formatting of cells and allows the sorting of unlocked cells:

   <source lang="vb">

Sub protect()

   ActiveWorksheet.Protect Password:="pass", AllowFormattingCells:=True, _
       AllowSorting:=True

End Sub

</source>
   
  

sort sheets

   <source lang="vb">

Sub SortSheets()

   Dim SheetNames()
   SheetCount = ActiveWorkbook.Sheets.Count
   ReDim SheetNames(1 To SheetCount)
   For i = 1 To SheetCount
       SheetNames(i) = ActiveWorkbook.Sheets(i).Name
   Next i
   Call BubbleSort(SheetNames)

End Sub

</source>
   
  

toggles the Visible property of the Text Box

   <source lang="vb">

Sub ToggleHelp()

   ActiveSheet.TextBoxes("HelpText").Visible = Not ActiveSheet.TextBoxes("HelpText").Visible

End Sub

</source>
   
  

To use the Copy function and Paste method, first insert the formula in the original cell as before, execute the Copy function of the Cells property, select the desired range, and paste the formula.

   <source lang="vb">

Sub copy()

   Cells(2, "B").copy
   Range("B2:B10").Select
   ActiveSheet.Paste

End Sub

</source>
   
  

Use For loop to fill a range

   <source lang="vb">

Sub FillRange3()

   Dim StartVal As Long
   Dim NumToFill As Long
   Dim CellCount As Long
   StartVal = 2
   NumToFill = 5
   For CellCount = 1 To NumToFill
       ActiveCell.offset(CellCount - 1, 0) = StartVal + CellCount - 1
   Next CellCount

End Sub

</source>
   
  

Uses the Not operator to effectively toggle the page break display from True to False and from False to True

   <source lang="vb">

Sub TogglePageBreaks()

   On Error Resume Next
   ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks

End Sub

</source>