For Calacuccia - Re Excel97

Calacuccia
You kindly provided me with a great bit of VBA to collapse/expand structured data.
My question was titled "Summarise Data" with the following ref:-
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20249330

The VBA has worked well BUT there are some minor niggles that I would like to sort.

The routine displays a menu bar with the button "Copy Visible Only". This works fine provided the correct cell reference is used.
If an incorrect cell ref is used the whole VBA errors.
If the cancel button is chosen the whole VBA errors.

For the whole VBA routine - if the user saves the file before clicking the "Back to Normal" button, then next time the file is opened the VBA errors. It would therefore be useful to run "Back to Normal" whenever the worksheet is closed.
I hope you can help.
Thanks BlueFin

Here's the code for reference

Dim nRows As Long, nColumns As Long
Dim anRange As Range
Sub CustomSummary()
Dim mBar As CommandBar, mButton As CommandBarControl
Dim tmpStr As String, tmpRow As Long
Dim i As Long, j As Long
On Error Resume Next
Set anRange = Application.InputBox("Please select range to work with :" & Chr(13) & Chr(13) & "Please select no header row, and the last column must contain data to summarize", "Data Selection", , , , , , 8)
If Err.Number <> 0 Then Exit Sub
Err.Clear
On Error GoTo 0
nRows = anRange.Rows.Count
nColumns = anRange.Columns.Count
On Error Resume Next
Set mBar = Application.CommandBars.Add("Custom Summarize", msoBarTop, False, True)
If Err.Number <> 0 Then
  Application.CommandBars("Custom Summarize").Delete
End If
Set mBar = Application.CommandBars.Add("Custom Summarize", msoBarTop, False, True)
Set mButton = mBar.Controls.Add(Type:=msoControlButton)
mButton.Caption = "More Detail"
mButton.Style = msoButtonCaption
mButton.OnAction = "MoreDetail"
mButton.Tag = "0," & nColumns - 1
Set mButton = mBar.Controls.Add(Type:=msoControlButton)
mButton.Caption = "Less Detail"
mButton.Style = msoButtonCaption
mButton.OnAction = "LessDetail"
mButton.Tag = "0," & nColumns - 1
Set mButton = mBar.Controls.Add(Type:=msoControlButton)
mButton.Caption = "Back to normal"
mButton.Style = msoButtonCaption
mButton.OnAction = "BackNormal"
Set mButton = mBar.Controls.Add(Type:=msoControlButton)
mButton.Caption = "Copy Visible Only"
mButton.Style = msoButtonCaption
mButton.OnAction = "SpecialCopy"
mBar.Visible = True
Err.Clear
On Error GoTo 0
Application.ScreenUpdating = False
anRange.Range(anRange.Cells(1, nColumns + 1), anRange.Cells(nRows, nColumns + nColumns - _
1)).Clear
For i = 1 To nColumns - 1
tmpStr = anRange.Cells(1, i)
tmpRow = 1
tmpValue = anRange.Cells(1, nColumns)
For j = 2 To nRows
    If anRange.Cells(j, i) <> tmpStr Or (i >= 2 And anRange.Cells(j - 1, nColumns + i - 1) <> "") Then
        anRange.Cells(j - 1, nColumns + i) = tmpValue
        tmpStr = anRange.Cells(j, i)
        tmpRow = j
        tmpValue = anRange.Cells(j, nColumns)
    Else
        tmpValue = tmpValue + anRange.Cells(j, nColumns)
    End If
    If j = nRows Then
        anRange.Cells(j, nColumns + i) = tmpValue
    End If
Next j
Next i
anRange.Range(anRange.Cells(1, nColumns + 1), anRange.Cells(1, nColumns + nColumns _
- 1)).EntireColumn.Hidden = True
Application.ScreenUpdating = True
End Sub

Sub MoreDetail()
Dim mTag As String
Dim fTag As Integer
Dim sTag As Integer
mTag = Application.CommandBars.ActionControl.Tag
fTag = CLng(Mid(mTag, 1, InStr(1, mTag, ",") - 1))
sTag = CLng(Mid(mTag, InStr(1, mTag, ",") + 1, Len(mTag) - InStr(1, mTag, ",")))
If fTag = 0 Then
  MsgBox "This is all the detail you can get!"
  Exit Sub
End If
Application.ScreenUpdating = False
For i = 1 To nRows
  If anRange.Cells(i, nColumns + nColumns - fTag + 1) <> "" Then
      anRange.Cells(i, 1).EntireRow.Hidden = False
  End If
Next i
anRange.Cells(1, nColumns + nColumns - fTag + 1).EntireColumn.Hidden = False
anRange.Cells(1, nColumns + nColumns - fTag).EntireColumn.Hidden = True
anRange.Cells(1, nColumns - fTag + 1).EntireColumn.Hidden = False
Application.CommandBars("Custom Summarize").Controls("More Detail").Tag = fTag - 1 & "," & sTag
Application.CommandBars("Custom Summarize").Controls("Less Detail").Tag = fTag - 1 & "," & sTag
Application.ScreenUpdating = True
Range("A1").Select
End Sub

Sub LessDetail()
Dim mTag As String
Dim fTag As Integer
Dim sTag As Integer
Dim i As Long
mTag = Application.CommandBars.ActionControl.Tag
fTag = CLng(Mid(mTag, 1, InStr(1, mTag, ",") - 1))
sTag = CLng(Mid(mTag, InStr(1, mTag, ",") + 1, Len(mTag) - InStr(1, mTag, ",")))
If fTag = sTag Then
  MsgBox "You've reached the minimum level of detail now!"
  Exit Sub
End If
Application.ScreenUpdating = False
For i = 1 To nRows
  If anRange.Cells(i, nColumns + nColumns - fTag - 1) = "" Then
      anRange.Cells(i, 1).EntireRow.Hidden = True
  End If
Next i
anRange.Cells(1, nColumns + nColumns - fTag).EntireColumn.Hidden = True
anRange.Cells(1, nColumns + nColumns - fTag - 1).EntireColumn.Hidden = False
anRange.Cells(1, nColumns - fTag).EntireColumn.Hidden = True
Application.CommandBars("Custom Summarize").Controls("More Detail").Tag = fTag + 1 & "," & sTag
Application.CommandBars("Custom Summarize").Controls("Less Detail").Tag = fTag + 1 & "," & sTag
Application.ScreenUpdating = True
Range("A1").Select
End Sub

Sub BackNormal()
anRange.Range("A1:A" & nRows).EntireRow.Hidden = False
anRange.Range(anRange.Cells(1, 1), anRange.Cells(1, nColumns + nColumns + 1)).EntireColumn.Hidden = False
'anRange.Range(anRange.Cells(1, nColumns + 1), anRange.Cells(nRows, nColumns + nColumns - 1)).Clear
anRange.Range(anRange.Cells(0, nColumns + 1), anRange.Cells(nRows, nColumns + nColumns - 1)).Clear
Application.CommandBars("Custom Summarize").Delete
End Sub

Sub SpecialCopy()
Dim pastRange As Range
Set anRange = anRange.Resize(nRows, nColumns + nColumns - 1)
Set pastRange = Application.InputBox("Select top left cell of destination range", "Pasting ... ", , , , , , 8)
anRange.SpecialCells(xlCellTypeVisible).Copy
pastRange.Parent.Paste pastRange
Application.CutCopyMode = False
End Sub