Archive for the ‘Tools Options’ Category.
In Excel, you can get the Username by using Application.UserName. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user's initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via that object model. That seems a little extreme to me.
This is what I'm using. It gets the first character of, up to, the first three words in Application.Username.
Public Function UserInitials() As String
Dim vaNames As Variant
Dim sInit As String
Dim lMax As Long
Dim i As Long
vaNames = Split(UCase(Application.UserName), " ")
lMax = Application.WorksheetFunction.Min(2, UBound(vaNames))
For i = 0 To lMax
sInit = sInit & Left$(vaNames(i), 1)
Next i
UserInitials = sInit
End Function
Some testing:
Recently I got a new computer at work. I had the occasion to use your comments at Power User Setup and it was great to not have to wrack my brain remembering all that stuff. There is one annoyance that should be added to the list: The blasted clipboard toolbar that appears when you copy more than once. I have absolutely no use for this thing. I wonder who does.
Using the information at How to prevent the Office Clipboard toolbar from appearing when you use multiple Copy commands in any Office 2000 program, I add the AcbControl DWORD item to HKey_CURRENT_USER\Software\Microsoft\Office\9.0\Common\General and set its base to Decimal and its value to 1.
I recently got a new (to me) PC at work. I went from 512 of RAM to 1G, which is quite nice. Anyway, I used the post and comments on Power User Setup to get Excel humming the way it was. One comment, by John Walkenbach, was that he unchecks the Move Selection After Enter box on the Edit menu of Tools > Options. Another commenter agreed that unchecked was the way to go. I've always had that checkbox checked, but it was time to try a new way. For a week, or so, I've worked with that box unchecked and I have to say that it was much easier...about half the time. It seems that half the time I want to remain in the cell I just edited and the other half I want to move down.
My new plan is to put a macro in Personal.xls (that will make three macros in there) to switch between the behaviors.
Sub ToggleMoveAfterEnter()
Application.MoveAfterReturn = Not Application.MoveAfterReturn
End Sub
I assigned it a shortcut key (Tools > Macros > Macros > Options) of Control+Shift+M. Now I can switch back and forth. We'll see how that goes.
Update: Mike Schaeffer shows some improvements here. I haven't seen Mike's blog before, but a quick scan of the post titles looks promising. I wanted to make sure there was a link to his blog in the main post rather than hidden in the comments.
The only thing I don't like about Mike's improvements is that it seems backward. It seems as if I'm punished for choosing correctly, i.e. I use the key combination to change the property and if it was the proper thing to do, I have to press it again. If the property was already set properly, I don't have to do anything. I think it should be the other way around, so I propose these changes:
Sub ToggleMoveAfterEnter()
With Application
.MoveAfterReturn = Not .MoveAfterReturn
.StatusBar = "Move After Return Is " & IIf(.MoveAfterReturn, "Enabled", "Disabled")
.OnTime Now + TimeValue("00:00:03"), "ResetStatusBar"
End With
End Sub
Sub ResetStatusBar()
Application.StatusBar = False
End Sub
In this scenario, I hit Control+Shift+M and look at the Status Bar. If I changed the property to what I want, I'm rewarded by not having to do anything. If I change the property away from what I want, I'm punished by having to press the keys again. Such are the delusions of some cosmic justice in my pathetic world.
I installed Office on a friend's computer the other day, and it got me thinking: What Excel setting do I change when I install? It's too long in between installs for me to remember. Usually, I just start using Excel then run into the same problems that caused me to change the setting in the first place. I started a list, but couldn't remember many of them. I guess it's time for a hard drive reformat so I can relearn this kind of stuff.
Here's the start of my list. Comment, if you will, on what settings you change right out of the gate:
- Tools - Options - General - uncheck Ignore other applications
- T - O - General - increase MRU to maximum of 9
- T - O - General - decrease Sheets in new workbook to minimum of 1
- T - O - General - change default file location
There are definitely other settings that I care about, but I can't remember if the default happens to be the same as the way I like it.
This one comes up all the time, and I thought it was worth a post. You enter a formula and it calculates correctly. Then you fill or copy the formula and you get the same answer, but you know the answer should be different.
Nine times out of 10, the problem is that calculation is set to manual. Take a look at the lower left of your spreadsheet.
If it says "Calculate", then calculation is set to manual and it needs to be automatic if you expect the formulas to change. Go to Tools > Options > Calculation and select the Automatic option button.
There is an option under Tools > Options called Precision As Displayed. Choosing this option changes the values in your cells to match the formatting of the cell.
For instance, if you have 41.234 in a cell and the cell is formatted to two decimal places (so it shows 41.23), this option will actually change the number to 41.23.
Back when I was a practicing CPA, we always had the problem of financial statements being one dollar off (the balance sheet must balance, you know). Nobody really cares about $1, but it's just shoddy workmanship to produce a financial statement that doesn't balance. So we would just plug a buck in somewhere and all would be well. My boss was a stickler about the Precision As Displayed option. It had to be on for financial statement workbooks or you would get a tongue lashing. Actually, he wanted it on for every workbook, but I don't swing that way.
For financial statements, I suppose it's not a bad policy. I'm really uncomfortable having a change in formatting change actual values. For that reason, I never use this option. I'm careful to include the ROUND function where necessary and that takes care of any problems.
Note also that the once Precision As Displayed is chosen, the actual numbers are gone forever. You can't ever Undo them. Heed the warning.