Logbook generates runtime error 438 on another network computer

[Jim Robertson]

I have developed a logsbook with 6 sheets for different workgroups to
status work. It is stored on a network drive and accessed by many
users. The logsheet was developed in excel 97. but recently the
company upgraded to Excel 2002 from excel 97 Now there are problems.
On most computers the workbook works fine but on one particular
machine attempting to access the listbox generates the runtime error.
I have been reading the groups and have seen some hints that it may be
a syntax problem. Please check the code below and offer any insights.
The code is driven by rectangle click events on all the pages and is
in the module so that it can be accessed by all sheets. BTW after the
one person gets the runtime error if he exits the book and reopens it
Excel repairs the workbook which consists of stripping all the macros
rendering them unusable for the rest of the workgroup. Thanks in
advance and sorry for being so long winded, its my first post!

Sub Caller()

Dim L As Double
Dim T As Double
s = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
Range(s).Activate


L = ActiveCell.Offset(0, -1).Left
T = ActiveCell.Top
ActiveSheet.ListBox1.Top = T
ActiveSheet.ListBox1.Left = L
ActiveSheet.ListBox1.Width = 100
ActiveSheet.ListBox1.Height = 180
ActiveSheet.ListBox1.Text = ""
ActiveSheet.ListBox1.Visible = True
End Sub

 

[Dave Peterson]

I copied your code into a general module and assigned to button and it worked ok
for me.

What line does your code break on?

===
But I don't think it's the code that's the problem. Can you start a new
workbook (from scratch) and set up a small test environment?

I'm guessing that xl2002 is more sensitive to corrupt (however slight) workbooks
and that's what's causing the crash.

 

[Jim Robertson]

Hi Dave: The code breaks at the first line of ActiveSheet.ListBox1.Top
= T and continues to break at the rest of them. I tried to build a new
workbook using the same code with the same results. I also tried to
change the order of the listbox statements ie. starting with
Activesheet.Listbox.Text = "" with no luck. I even copied the code to
each individual worksheet and reassigned the macros for all the
rectangles but no change. Is there a way of setting up error handling
statements so that xl2002 doesn't FIX my workbook by stripping out the
code? At least that way the rest of the users will still be able to
access it. Thanks in advance, Jim.

 

[Dave Peterson]

If that user who has the problem doesn't save the file, I think that the
original workbook will be intact. (So tell that user to never save when they
get that message.)

But that doesn't help resolve the problem.

Can you find the pc that's causing the trouble and load up the workbook. Then
go to the VBE and look under Tools|References.

Since you're using a listbox from the control toolbox toolbar, you should see a
reference to:

Microsoft Forms x.x Object Library
(Using xl2002, I get 2.0 as the x.x)

Maybe that's missing from that user's installation.

If you create a new workbook on that pc and then add a listbox to a worksheet,
can you get similar code to work?

 

[Jim Robertson]

Dave: We had our IT guys reinstall the office XP on the offending
computer and, touch wood, the problem seems to be gone. Looks like his
version may have been corrupt, at least something was missing from his
installation. Thanks for the tip!