Archive
Tag "Excel"

So I finally got around to installing Office 2007. This is what it looked like:

Office 2007 - First Look

Office 2007 — First Look

Now I don't know about you, but to me this bloo-ey look is hideous.I am not on Vista yet, by choice, so that sky blue gra­da­tion thing going on the top was not my cuppa. Why soft­ware designed for a cer­tain plat­form can­not honor a user's gen­eral sys­tem UI pref­er­ences is beyond me, but Office 2007 does insist on hav­ing it's own look and feel. As though the new rib­bon clut­ter was not enough.

I wanted to get rid of those rib­bons to begin with. So I down­loaded the the free ver­sion of Rib­bon Cus­tomizer. They offer some Pro ver­sion but it does things I don't par­tic­u­larly care about. Alter­na­tively, there is Tool­bar­Tog­gle, but on their site I did not seem to catch a free ver­sion, and I was unwill­ing to pay for this stuff.

The Rib­bon­Cus­tomizer install is pretty straight­for­ward and when you start Word 2007 after its instal­la­tion, here is how Word looks. There is an addi­tional item in the View menu at the end:

Word 2007 after RibbonCustomizer

Word 2007 after RibbonCustomizer

I clicked on the obvi­ous menu option to make Clas­sicUI my first menu tab. This is what this does:

Classic 2003 interface

Clas­sic 2003 interface

That's a good start, but I now wanted to clean up some other stuff. For­tu­nately, Microsoft chose to include the addi­tional "Min­i­mize Rib­bon" fea­ture, which con­tex­tu­ally hides the rib­bon when your focus is on writ­ing inside the doc­u­ment. So let's do that:

Minimize the Word 2007 ribbon

Min­i­mize the Word 2007 ribbon

Now to get rid of the forced Blue. Click on the "More Com­mands" option in the menu shown in the screen­shot above. Choose Sil­ver and make other adjust­ments to your taste:

Choose silver

Choose sil­ver

Now this is what Word 2007 looks like, with min­i­mized rib­bon, clas­sic 2003 UI, and a some­what less intru­sive sil­ver gradient:

Phew. I also rec­om­mend set­ting the default "Save" options as your reg­u­lar Word ".doc" instead of the new ".docx" (or other .xlsx and .pptx equiv­a­lents) as that is a bit more stan­dard even today in 2008.

Read More

Have slow load­ing Word doc­u­ments, or pass­word pro­tected Excel spread­sheets for which you have for­got­ten a pass­word? Who'd have thunk of this solu­tion to your MS Office woes..

Read More

Here's a cool, handy macro to auto-generate a Table of Con­tents for any Excel file.

Read More

Here's a handy macro to make a new work­sheet inside your Excel file, then tra­verse through each and every sheet in the file, col­lect­ing all func­tions and for­mu­las used in the whole file. All these for­mu­las are listed on a sep­a­rate work­sheet in the same file.

Here's a handy macro to make a new work­sheet inside your Excel file, then tra­verse through each and every sheet in the file, col­lect­ing all func­tions and for­mu­las used in the whole file. All these for­mu­las are listed on a sep­a­rate work­sheet in the same file.

The code is below, feel free to use it but please attribute when­ever you use it, thanks –

Option Explicit
Public Sub ListFormulasInWorkbook()
Const SHEETNAME As String = "Formulas in *"
Have fun!
Const ALLFORMULAS As Integer = _
xlNumbers + xlTextValues + xlLogical + xlErrors
Const maxRows As Long = 65500
Dim formulaSht As Worksheet
Dim destRng As Range
Dim cell As Range
Dim wkSht As Worksheet
Dim formulaRng As Range
Dim shCnt As Long
Dim oldScreenUpdating As Boolean
With Application
oldScreenUpdating = .ScreenUpdating
.ScreenUpdating = False
End With
shCnt = 0
ListFormulasAddSheet formulaSht, shCnt
' Enumerate formulas on each sheet
Set destRng = formulaSht.Range("A4")
For Each wkSht In ActiveWorkbook.Worksheets
If Not wkSht.Name Like SHEETNAME Then
Application.StatusBar = wkSht.Name
destRng.Value = wkSht.Name
Set destRng = destRng.Offset(1, 0)
On Error Resume Next
Set formulaRng = wkSht.Cells.SpecialCells( _
xlCellTypeFormulas, ALLFORMULAS)
On Error GoTo 0
If formulaRng Is Nothing Then
destRng.Offset(0, 1).Value = "None"
Set destRng = destRng.Offset(1, 0)
Else
For Each cell In formulaRng
With destRng
.Offset(0, 1) = cell.Address(0, 0)
.Offset(0, 2) = "'" & cell.Formula
.Offset(0, 3) = cell.Value
End With
Set destRng = destRng.Offset(1, 0)
If destRng.row > maxRows Then
ListFormulasAddSheet formulaSht, shCnt
Set destRng = formulaSht.Range("A5")
destRng.Offset(-1, 0).Value = wkSht.Name
End If
Next cell
Set formulaRng = Nothing
End If
With destRng.Resize(1, 4).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Set destRng = destRng.Offset(1, 0)
If destRng.row > maxRows Then
ListFormulasAddSheet formulaSht, shCnt
Set destRng = formulaSht.Range("A5")
destRng.Offset(-1, 0).Value = wkSht.Name
End If
End If
Next wkSht
With Application
.StatusBar = False
.ScreenUpdating = oldScreenUpdating
End With
End Sub
Private Sub ListFormulasAddSheet( _
formulaSht As Worksheet, shtCnt As Long)
Const SHEETNAME As String = "Formulas in "
Const SHEETTITLE As String = "Formulas in $ as of "
Const DATEFORMAT As String = "dd MMM yyyy hh:mm"
Dim shtName As String
With ActiveWorkbook
' Delete existing sheet, create new
shtCnt = shtCnt + 1
shtName = Left(SHEETNAME & .Name, 28)
If shtCnt > 1 Then _
shtName = shtName & "_" & shtCnt
On Error Resume Next
Application.DisplayAlerts = False
.Worksheets(shtName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set formulaSht = .Worksheets.Add( _
after:=Sheets(Sheets.Count))
End With
With formulaSht
' Format headers
.Name = shtName
.Columns(1).ColumnWidth = 15
.Columns(2).ColumnWidth = 8
.Columns(3).ColumnWidth = 60
.Columns(4).ColumnWidth = 40
With .Range("C:D")
.Font.Size = 9
.HorizontalAlignment = xlLeft
.EntireColumn.WrapText = True
End With
With .Range("A1")
.Value = Application.Substitute(SHEETTITLE, "$", _
ActiveWorkbook.Name) & Format(Now, DATEFORMAT)
With .Font
.Bold = True
.ColorIndex = 5
.Size = 14
End With
End With
With .Range("A3").Resize(1, 4)
.Value = Array("Sheet", "Address", "Formula", "Value")
With .Font
.ColorIndex = 13
.Bold = True
.Size = 12
End With
.HorizontalAlignment = xlCenter
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 5
End With
End With
End With
End Sub

Have fun!

Read More

So you have Office XP, but can­not get it to update from Microsoft's Office Update web­site? Here are some ways to patch­ing MS Office in such a way that it accepts all cur­rently released updates as well.

Read More