Archive for the ‘Window Object’ Category.
When I do some screen shots, I need to hide the cursor. Not only do I want no cell selected, but I don't want the column or row headers highlighted - so I can't just page down for instance. Manually, I was using Ctrl+End to get to the last cell followed by a few tabs and a few page downs. Then I'd click the scroll bars to get A1 back into view. Being the mouse-a-phobe that I am, you can imagine how infuriating this is. Here's the macro I use now
Sub HideCursor()
With ActiveSheet.UsedRange
.Cells(.Rows.Count + 100, .Columns.Count + 100).Select
End With
ActiveWindow.ScrollIntoView 0, 0, 1, 1
End Sub
Is there a way to do this without a macro that I'm missing? Is there a better way to do this with a macro?
Right now I'm in the middle of closing out the month. That's accounting lingo for reconciling the accounts and preparing financial statements. Most of my reconciling is done in one workbook with a worksheet for each account. The last sheet, titled AJE, is a list of my adjusting journal entries. (Oh boy! This sounds like an accounting lesson. Fun!)
Many of my entries are the same from month to month, except for the dollar amounts, so I have the skeleton of the entry on the AJE sheet. The reconciliation sheet more or less creates the entry, or at a minimum, has the information to create the entry. I'm always switching back and forth between the AJE sheet and the reconciliation sheet. I can't seem to remember three numbers long enough to type the entry, so I wanted to make the AJE sheet active but still be able to see the reconciliation sheet.
Now I can type in my AJE and still see the numbers I'm supposed to be typing. When I'm done, Cntl+F4 to close the window and Cntl+F10 to re-maximize the main window. Here's the code:
Sub CreateWindow()
Dim wnParent As Window
Dim wnChild As Window
'make a reference to be used later
Set wnParent = ActiveWindow
'create a new window that will be the smaller window
wnParent.NewWindow
Set wnChild = ActiveWindow
'change the size of the windows
With Application
wnChild.WindowState = xlNormal
wnChild.Top = .Top + (.Height * 0.2)
wnChild.Height = .Height * 0.4
wnChild.Left = .Left + .Width * 0.6
wnChild.Width = .Width * 0.35
wnParent.Top = 1
wnParent.Left = 1
wnParent.Height = .Height * 0.85
wnParent.Width = .Width * 0.95
End With
End Sub
If you've ever worked with more than one workbook, or even multiple windows per a workbook, you'll probably have used Window arrangements.
You can arrange the windows by selecting Arrange from the Window menu.
With VBA you can arrange your windows using:
Windows.Arrange ArrangeStyle:=xlArrangeStyleTiled
Arrangements come in a few styles: xlArrangeStyleCascade, xlArrangeStyleTiled, xlArrangeStyleHorizontal, xlArrangeStyleVertical
For more control over your tiling, you could use the following code:
'Example best with 3 or more windows
Sub test_windows()
ActiveWindow.WindowState = xlNormal 'maximised windows cannot be resized
'tile the usable area with 2 columns
Tiler Windows, 0, 0, Application.UsableWidth, Application.UsableHeight, , 2
End Sub
Sub Tiler(ObjColl As Object, OffsetX As Double, OffsetY As Double, _
UsableWidth As Double, UsableHeight As Double, _
Optional Rows As Long = 0, Optional Cols As Long = 0)
Dim i As Long, blnByCols As Boolean
Dim lngPri As Long, lngSec As Long, lngPriRemainder As Long
Dim dblPriMax As Double, dblSecMax As Double
Dim dblPriStart As Double, dblSecStart As Double
Dim dblPriLen As Double, dblSecLen As Double
If Cols = 0 And Rows = 0 Then Exit Sub
blnByCols = Not Cols = 0
lngPri = IIf(blnByCols, Cols, Rows)
dblPriMax = IIf(blnByCols, UsableWidth, UsableHeight)
dblSecMax = IIf(blnByCols, UsableHeight, UsableWidth)
lngPriRemainder = ObjColl.Count Mod lngPri
lngSec = -Int(-ObjColl.Count / lngPri)
dblSecLen = dblSecMax / lngSec
For i = 0 To ObjColl.Count - 1
If i>= ObjColl.Count - lngPriRemainder Then
dblPriStart = dblPriMax / lngPriRemainder * ((i Mod lngPri) Mod lngPriRemainder)
dblPriLen = dblPriMax / lngPriRemainder
Else
dblPriStart = dblPriMax / lngPri * (i Mod lngPri)
dblPriLen = dblPriMax / lngPri
End If
dblSecStart = (dblSecMax / lngSec) * Int(i / lngPri)
ObjColl(i + 1).Left = IIf(blnByCols, dblPriStart, dblSecStart) + OffsetX
ObjColl(i + 1).Top = IIf(blnByCols, dblSecStart, dblPriStart) + OffsetY
ObjColl(i + 1).Width = IIf(blnByCols, dblPriLen, dblSecLen)
ObjColl(i + 1).Height = IIf(blnByCols, dblSecLen, dblPriLen)
Next
End Sub
I've made the Tiler procedure generic enough to take any collection of rectangular objects.
The collection must have the Count property and each item must have Left, Top, Width and Height properties.
For example, it can be used against a collection of Charts.
Sub test_charts()
Tiler ActiveSheet.ChartObjects, 100, 100, 500, 500, 2
End Sub
In the UI, you can arrange the open windows using Windows>Arrange. In VBA, the same effect is achieved using the Arrange method of the Windows collection object.
Application.Windows.Arrange xlArrangeStyleTiled
The argument is an xlArrangeStyle constant. What I haven't been able to do is determine the current state of the windows. That is, there's no ArrangeStyle property to read.
To cycle through some different arrangements, you can store the current ArrangeStyle in a variable and use that variable to switch to another style. In this example, the windows are cycled through normal maximized view, tiled arrangement, and horizontal arrangement.
Dim mlWndState As Long 'Module level variable
Sub SwitchView()
'Cycle through Maximized, Tiled, and Horizontal
Select Case mlWndState
Case xlMaximized
Application.Windows.Arrange xlArrangeStyleTiled
mlWndState = xlArrangeStyleTiled
Case xlArrangeStyleTiled
Application.Windows.Arrange xlArrangeStyleHorizontal
mlWndState = xlArrangeStyleHorizontal
Case Else
Application.ActiveWindow.WindowState = xlMaximized
mlWndState = xlMaximized
End Select
End Sub