Office provides a rich set of objects, methods, and properties that allow developers to return and set content within an Office document. For example, in Word, solutions can parse through a document and retrieve such information as the number of words, the text styles, and the font formatting. Until Office 2000 was created, there was no programmatic way for developers to determine where the document content was displayed on the user's monitor. The developer could obtain text from a paragraph in a Word document but couldn't determine where that text was on the screen—or if it was even visible to the user.
Why would you care? There are a number of scenarios in which you'll find that capability useful. The most common one is the Find and Replace functionality in Word, Excel, or PowerPoint. When you start a search for a text string, every time the application finds a matching text string, it displays and highlights the text. Note also that the Find and Replace dialog box is repositioned so that it never covers the highlighted text. The Spelling and Grammar dialog box also behaves like the Find and Replace dialog box.
When the spelling checker in Word, Excel, or PowerPoint finds an incorrect word, the application automatically highlights and displays the word (that is, the selection becomes visible on the screen). For example, suppose you have a three-page document loaded in Word and the page in view is the first. If the spelling checker finds a word spelled incorrectly on page three, the word is highlighted and Word scrolls down until the highlighted word is visible.
In addition, as a user you use the vertical scroll bar at the side of the window until you find the word you want to see. In order to add functionality to your solutions that allow you to either mimic the spelling checker or to scroll through the window as a user does, you can use the ScrollIntoView method in Word, Excel, or PowerPoint. The ScrollIntoView method scrolls through the document window so that a specified object, whether it's text, a cell, or a shape, is displayed in the active window. One common scenario that might use ScrollIntoView is a document reader that scrolls through the text as it reads the text selection, passing the text selection to a text-to-speech engine on your machine.
The ScrollIntoView method in Word is a member of the Window object, and two arguments are passed into it. The first argument, named obj, is defined as type Object. This means you can pass an object of either type Range or type Shape. More generically, you can pass either text or a shape (such as an AutoShape or a Picture). The second argument, named Start, defined as type Boolean, is optional.
This argument specifies which corner of the bounding rectangle of the text range or shape takes viewing precedence when the bounding rectangle is bigger than the physical size of the window on screen. A value of True indicates that the top left of the bounding rectangle should be scrolled into view so that it appears at the top left of the window. If the value passed as the Start argument is False, the window should be scrolled so that the bottom right of the rectangle appears at the window's bottom right. If the rectangle is larger than the screen, the Start Boolean will determine which part of the rectangle will be in view.
ActiveWindow.ScrollIntoView _
obj:=ActiveDocument _
.Paragraphs.Last.Range, _
Start:=True
|
Sub ScrollToSelection()
Dim rngText As Word.Range
Set rngText = ActiveWindow.Document _
.Paragraphs.Last.Range
rngText.Select
ActiveWindow.ScrollIntoView rngText
End Sub
|
In this procedure, the last paragraph of the document is selected and the active window scrolls to view it. If the text selection bounds fit into the visible window, the ScrollIntoView method will scroll the text selection so that all of it is visible. Even if part of the selection isn't visible when the procedure starts (that is, a paragraph is selected but only the first line or two is visible at the bottom of the window), using the ScrollIntoView method will scroll the window until all of the text is visible (unless the text bounds of the paragraph are larger than the window).
The ScrollIntoView method in Excel is a member of the Window object, but instead of taking two arguments (obj and Start), as Word does, it takes five. The first four arguments represent the coordinates and size of the object. In this 0regard, PowerPoint is like Excel.
So what's the difference between Word and Excel/PowerPoint? The coordinates and size of the object passed into Word's ScrollIntoView method are called implicitly for you. In Excel and PowerPoint, you explicitly pass in the coordinates and size of the object you want to scroll into view. Word's method is more efficient when it comes to the amount of code you have to write.
The ScrollIntoView method in Excel is a member of the Window object as well as a member of the Pane object. In Excel, you can split the window into multiple panes, and most of the members of the Window object are also a member of the Pane object (because the Pane acts like a window in the window). The first four arguments passed into the ScrollIntoView method are Left, Top, Width, and Height and are defined as type Long.
ActiveWindow.ScrollIntoView Left:=0, Top:=0, _
Width:=100, Height:=100, Start:=True
|
The Left argument represents the left value of the bounding rectangle in points along the horizontal of the worksheet window. The Top argument is the top value of the bounding rectangle, expressed in points along the vertical of the worksheet window. The Width argument is the width of the bounding rectangle, expressed in points along the horizontal of the window. And the Height is the height value of the bounding rectangle, expressed in points along the vertical of the window.
Finally, the fifth and last argument of the ScrollIntoView method is the same as the second argument in Word's ScrollIntoView method. It's named Start; it's declared of type Boolean; and it specifies which end of the bounding rectangle is important. The default value is True, indicating that the top left of the rectangle scrolls so that it appears at the top left of the window. If the value of False is passed into the Start argument, the window scrolls so that the bottom right of the rectangle appears at the bottom right of the window. If the rectangle is larger than the screen, the Start Boolean will determine which part of the rectangle will be in view.
Sub ScrollSelection()
With ActiveWindow.Selection
ActiveWindow.ScrollIntoView .Left, .Top, _
.Width, .Height, True
End With
End Sub
|
Excel will scroll into view the selected cells, whether they constitute a contiguous selection of cells or not. Note that this sample code also works if you have a shape such as an AutoShape or Picture selected.
In PowerPoint, the ScrollIntoView method is defined as it is in Excel. That is, there are five arguments passed to it. The first four are the coordinates and the size of the object, and the fifth is the Start Boolean. You can use the ScrollIntoView method only in a view that can contain a shape selection.
Sub ScrollSelection()
If ActiveWindow.Selection _
.Type = ppSelectionShapes Then
With ActiveWindow.Selection.ShapeRange(1)
ActiveWindow.ScrollIntoView .Left, .Top, _
.Width, .Height
End With
End If
End Sub
|
This code works when only one shape is selected. If the shape selection bounds fit into the visible window, the ScrollIntoView method will scroll the shape selection so that all of it is visible.
When you use the spelling checker in a document, an application like Word will display the Spelling and Grammar dialog box the first time the spelling or grammar checker finds an incorrect fragment of text. When the dialog box is displayed, the text fragment is scrolled into view and selected, and the Spelling and Grammar dialog box is positioned so that it doesn't overlap the selection.
In some cases, you may need to move a custom dialog box so it doesn't cover the text selection. In order to position a dialog box, you need to determine where content such as text, a cell, or a shape is located with respect to the top-left position on the screen. Note that the Left and Top property values of a dialog box in Visual Basic 6.0 or UserForms in the Visual Basic Editor are specified with respect to the top-left of the screen. Thus, knowing where content in a Word, Excel, or PowerPoint document is with respect to the top-left of the screen helps position custom dialog boxes around content visible to the user.
To determine where content in the active document window is in Word, you need to use the GetPoint method on the Window object. The GetPoint method takes five arguments. The first four are variables declared as Long, which represent the left coordinate, top coordinate, width, and height of the content on the screen. You declare the coordinate variables, and after the GetPoint method is executed, Word fills in the variables with the return values.
The last argument is the object that represents content in the document of a window. The object must be of type Range or Shape. So you can pass in either an object representing text in the document in the active window or a shape floating on top of and below text. If the content is visible on the screen, GetPoint returns in units of pixels the screen coordinates of the content. If the content passed into the last argument of the GetPoint method is a text range, the coordinates of the virtual, rectangular bounding box around the text is returned.
Of the following two procedures, the first, GetSelection, determines the selection type in the active window. If the selection is one of the types listed in the Case statement, the range or shape (depending on the selection) is passed to the second procedure, called MoveAssistant. The GetPoint method in the second procedure returns the position of the text range or shape with respect to the top-left of the screen. The If…Then statement checks to see if the object's left or top coordinate is off the screen, and thus, not all the content is visible. If this isn't the case, the procedure moves the position of the Office Assistant closer to the top-left coordinates of the content.
Add the following procedures to a module in the Visual Basic Editor and switch back to Word. Press ALT+F8 to display the Macros dialog box, select the GetSelection procedure and then click Run. The left and top position of the Office Assistant will be close to the top-left position of the selection.
Sub GetSelection()
Select Case ActiveWindow.Selection.Type
Case wdSelectionNormal, wdSelectionShape, _
wdSelectionIP
MoveAssistant ActiveWindow _
.Selection.Range
End Select
End Sub
Sub MoveAssistant(objSel As Object)
Dim lLeft As Long, lTop As Long
Dim lWidth As Long, lHeight As Long
On Error Resume Next
ActiveWindow.GetPoint lLeft, lTop, _
lWidth, lHeight, objSel
If lLeft < 0 Or lTop < 0 Then
Debug.Print "Content not fully visible."
Else
With Application.Assistant
.Visible = True
.Move lLeft, lTop
End With
End If
End Sub
|
In Excel and PowerPoint, developers can use the PointsToScreenPixelsX and PointsToScreenPixelsY properties to determine where text, cells, or shapes are located on the screen. These properties convert units of points (document coordinates) to screen pixels. They are members of the Window object in Excel and the corresponding DocumentWindow object in PowerPoint. The associated GetPoint method in Word is a member of the Window object. The PointsToScreenPixelsX and PointsToScreenPixelsY properties require that the text, cell, or shape in question be located on the active slide in PowerPoint or on the active sheet in Excel.
The following procedures are similar in function to the ones in Word. In the MoveAssistant procedure, the GetPoint method is replaced with the two PointsToScreenPixel methods. The MoveAssistant procedure is exactly the same in Excel and PowerPoint. The only difference between Excel and PowerPoint is the code in the GetSelection procedure, as listed below, to retrieve the current selection.
' For EXCEL only
Sub GetSelection()
Select Case TypeName(ActiveWindow.Selection)
Case "Range"
MoveAssistant ActiveWindow _
.Selection
End Select
End Sub
' For POWERPOINT only
Sub GetSelection()
Select Case ActiveWindow.Selection.Type
Case ppSelectionShapes
MoveAssistant ActiveWindow _
.Selection.ShapeRange.Item(1)
End Select
End Sub
' For EXCEL and POWERPOINT
Sub MoveAssistant(objSel As Object)
Dim lLeft As Long, lTop As Long
On Error Resume Next
With ActiveWindow
lLeft = .PointsToScreenPixelsX(objSel.Left)
lTop = .PointsToScreenPixelsY(objSel.Top)
End With
If lLeft < 0 Or lTop < 0 Then
Debug.Print "Content not fully visible."
Else
With Application.Assistant
.Visible = True
.Move lLeft, lTop
End With
End If
End Sub
|