VBA/Excel/Access/Word/Excel/ActiveWorksheet — различия между версиями

Материал из VB Эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 12:47, 26 мая 2010

Adds two hyperlinks to active sheet

 
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



All properties of PageSetup

 
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



Change the name of the active worksheet

 
Sub changeName()
    ActiveSheet.name = "My Sheet"
End Sub



Changing Boolean settings

 
Sub Main()
    ActiveSheet.DisplayPageBreaks = False
    ActiveSheet.DisplayPageBreaks = True
End Sub



Defined a print area

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



Inserts the sheet names into the SheetNames array

 
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



Macro for changing page setup to landscape orientation

 
Sub Macro2()
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With
End Sub



Protecting a Worksheet

 
Sub protect()
    ActiveWorksheet.Protect Password:="pass"



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

 
Sub protect()
    ActiveWorksheet.Protect Password:="pass", AllowFormattingCells:=True, _
        AllowSorting:=True
End Sub



sort sheets

 
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



toggles the Visible property of the Text Box

 
Sub ToggleHelp()
    ActiveSheet.TextBoxes("HelpText").Visible = Not ActiveSheet.TextBoxes("HelpText").Visible
End Sub



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.

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



Use For loop to fill a range

 
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



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

 
Sub TogglePageBreaks()
    On Error Resume Next
    ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks
End Sub