Terry,
I suspect it has to do with visibility..
Remember ANY workbook(visible/hidden or addin) needs at least 1 visible
sheet.
So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..
Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.
Note an addin will not appear in workbooks collection
Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object
If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If
With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With
If Not sh Is Nothing Then sh.Activate
End Sub
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage:
http://members.chello.nl/keepitcool
"Terry von Gease" wrote:
Sub new_scratch()
Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate
End Sub