Not that simple.
The malfeasant code is but a drop in a bucket of well over 2Mb of VBA, plus
actual worksheets, that was created on a machine running XP Pro and Office
Pro 2002. The entire thing consists of three xls files, one big bugger with
all the code, one for a persistent list of names, and one with worksheets
with which the user interacts. The supporting files eventually will evolve
into xla files, but not at this time.
It was zipped up and sent off to a confederate running Windows 2000 and
Excel 2000. There it exhibits this strange behavior.
Moreover it did EXACTLY the same thing when it was tried on another machine
running Excel 2000 on Windows 98 as well as windows 2000 on that machine. In
this case when the Excel was upgraded to 2002 the problem went away.
I full well realize that there's nothing wrong with the code, It's something
about the version mix that would appear to be the culprit. Nonetheless, it
has to run on both 2000 and 2002.
Perhaps it needs to be opened with macros disabled and then compiled and
saved on the machine running Excel 2000. Would this force Excel to
reconsider the code in any way different than just using it? Is there any
way to convince my Excel 2002 that it's really Excel 2000 If so, would it do
any good? Doing a 'save as' into one of the myriad of other formats
provided?
--
Terry
"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley
"Tom Ogilvy" wrote in message
...
the code worked flawlessly for me in Excel 2000 and Excel 97.
Try it in a new workbook.
I guess you will have to figure out what is different.
Sure you don't have On Error Resume Next set somewhere above this so any
error is suppressed?
--
Regards,
Tom Ogilvy
Terry von Gease wrote in message
...
Thanks for the reply and the work, but most likely that isn't the
probelm.
Here's some additional information:
It's not an addin, it's an xls file.
There's ALWAYS at least two other sheets visible at all times.
Scratch is ALWAYS hidden except at that moment when it's created via the
add
and the subsequent instruction to set it's visibility to False has yet
to
execute. Moreover, the delete of scratch works just fine, it's the add
that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving
the
illusion that all is well.
There is NO error generated, the Worksheets.Add gives no indication that
it
failed to function, yet it fails to function.
The code works flawlessly on Excel 2002, it does not function properly
on
Excel 2000. That's my big problem here.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^
For a little history, the reason that I have to delete and then add the
sheet is because this sheet is prone to corruption. If merely cleared,
all
cells deleted, or what-have-you it ends up that inexplicably, from time
to
time, the cell A1 refuses to contain anything. That It has nothing to do
with formats, macros, or any other thing, the cell simply will not
contain
anything. No matter what the state of the sheet or the cell or how you
try
to get something into the cell. It refuses to contain anything. I cast
this
bread upon both these and Microsoft's waters and no cogent explanation
for
this phenomena or how to make it go away was forthcoming. Hence the
crude
and hideously inelegant delete and add.
I wish I could get rid of this entire procedure, it's existence offends
me.
But I can't seem to keep things working on the scratch sheet without
doing
the delete and add. That being the case, I really need to get this
working
on 2000 ASAP.
I'll entertain any reasonable alternative.
--
Terry
"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley
"keepitcool" wrote in message
...
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