Display Oriented Features That Are Nice to Know

Although I use the ScreenUpdating and StatusBar properties in nearly every project of significance, I seldom use the remaining display-oriented features. These features deal primarily with manipulating or obtaining information about windows (the user interface element, not the operating system).

The first property I'd like to mention in this section deals with the cursor. In fact, it is referred to as the Cursor property. You can use the Cursor property to change or determine which mouse icon is used in Excel. Listing 5.3 demonstrates the various cursors available.

Listing 5.3: Cursors Available to Use with the Cursor Property

Sub ViewCursors()

Application.Cursor = xlNorthwestArrow

MsgBox "Do you like the xlNorthwestArrow? Hover over the worksheet to see it." Application.Cursor = xlIBeam

MsgBox "How about the xlIBeam? Hover over the worksheet to see it." Application.Cursor = xlWait

MsgBox "How about xlWait? Hover over the worksheet to see it."

Application.Cursor = xlDefault MsgBox "Back to the default..." End Sub

As with the status bar, you need to make sure that when you change the cursor you change it back to the default. This can be tricky when errors occur if your error-handling code isn't working correctly. Many times you may switch the cursor to the wait (hourglass) cursor. If you don't change the cursor back to the default, your users may think that the application is hung.

Listing 5.4 demonstrates some of the various window-oriented properties of the Application object. The first property you see in the procedure is WindowState. This property is a read/write property that allows you to determine or change the state of Excel's window. You can set it using the defined constants xlMaximized, xlMinimized, and xlNormal. If you are determining the state of the window, WindowState returns a value that you can use directly or compare against the defined values as shown in the Select Case statement in Listing 5.4.

Listing 5.4: Demonstration of Various Window-Oriented Properties

Sub GetWindowInfo() Dim lState As Long Dim sInfo As String Dim lResponse As Long

' Determine window state lState = Application.WindowState Select Case lState Case xlMaximized sInfo = "Window is maximized." & vbCrLf Case xlMinimized sInfo = "Window is minimized." & vbCrLf Case xlNormal sInfo = "Window is normal." & vbCrLf End Select

' Prepare message to be displayed sInfo = sInfo & "Usable Height = " & _

Application.UsableHeight & vbCrLf sInfo = sInfo & "Usable Width = " & _

Application.UsableWidth & vbCrLf sInfo = sInfo & "Height = " & _

Application.Height & vbCrLf sInfo = sInfo & "Width = " & _

Application.Width & vbCrLf & vbCrLf sInfo = sInfo & "Would you like to minimize it?"

' Display message lResponse = MsgBox(sInfo, vbYesNo, "Window Info")

' Minimize window if user clicked Yes If lResponse = vbYes Then

Application.WindowState = xlMinimized End If End Sub

The next four properties used in Listing 5.4 are Height, Width, UsableHeight, and UsableWidth. Height and Width return the height and width of Excel's main application window. Meanwhile UsableHeight and UsableWidth return the maximum height and width that a window can assume within the main application window. Height and Width can only be changed if WindowState = xlNormal. UsableHeight and UsableWidth are read-only. To put all of this information into a single message box, I prepared the message using the defined constant vbCrLF, which places a line-feed character in my text and forces the message box to break the text into multiple lines of text.

0 0

Post a comment