Archive for the ‘Worksheet 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?
In the UI, you have some formatting options when entering a custom footer (File > Page Setup > Header/Footer). You also have those options when creating a footer in VBA, you just have to know the codes to use.
Special codes like page numbers and dates can be found in the constant definition section of the code below. It's an ampersand followed by a letter (A for the sheet name, who came up with that?).
For fonts and font sizes, the format is
ampersand + font name in double quotes + ampersand + font size
which is all optional, that is, you only need to include the name or the size if you want to change them. I know exactly two font names from memory, so if you're like me the easiest way to set this up is to do it in the UI and check the LeftFooter, CenterFooter, and/or RightFooter properties of the PageSetup object. Of course, all this applies to headers as well.
Here's an example that creates a formatted footer and uses some (all?) of the special codes.
Sub CreateFooters()
Dim sLeft As String
Dim sCenter As String
Dim sRight As String
Const sPAGE As String = "&P"
Const sPAGES As String = "&N"
Const sFILE As String = "&F"
Const sSHEET As String = "&A"
Const sDATE As String = "&D"
Const sTIME As String = "&T"
sLeft = sPAGE & " of " & sPAGES
sCenter = "&""Albertus Medium,Bold""&11This&""Arial,Regular""&10 is formatted text"
sRight = "[" & sFILE & "]" & sSHEET & Chr$(10) & sDATE & " " & sTIME
With Sheet1.PageSetup
.LeftFooter = sLeft
.CenterFooter = sCenter
.RightFooter = sRight
End With
End Sub
The For Each construct can be used to loop through a range. By limiting the range, you can speed up your code. The Intersect function is a good way to limit the range. For instance, to convert everything in column A to proper case, you can use a sub like this:
Sub ConvertAToProper()
Dim rCell As Range
For Each rCell In Sheet1.Columns(1).Cells
If Not IsEmpty(rCell.Value) Then
rCell.Value = StrConv(rCell.Value, vbProperCase)
End If
Next rCell
End Sub
To speed up the code, limit the looping range like this:
For Each rCell In Intersect(Sheet1.Columns(1), Sheet1.UsedRange).Cells
Since cells outside of the UsedRange couldn't possibly pass the IsEmpty test, limiting the range to the UsedRange makes the loop faster.