в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:37Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/ActiveWorksheet - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия