User Form created on PC crashes on Office 2004 for Mac

[Tim Archer]

I have written an extensive program which uses User Forms
within VB to give the user control. I used Windows XP
with Office 2003 to write the program. My boss uses a
Mac and I managed to get it to work on System 9 after a
few hiccups. He has just upgraded to System 10 and has
installed Office 2004. The User Form loads when you
press the button to launch it and the radio buttons all
work. However, Excel crashes whenever you try to enter
any text into the text boxes. The spreadsheet still
works if he uses the older version of Office (2001 I
think). Is this something that anyone has seen before?
Any help is much appreciated.

Regards,

Tim

 

[JE McGimpsey]

Is this something that anyone has seen before?
Any help is much appreciated.

I haven't, but there are several bugs associated with Userforms in XL04.

http://www.mcgimpsey.com/macoffice/excel/04/xl04bugs.html

Without seeing the code behind the userform/textboxes, it's hard to say
what the problem is.

 

[Tim Archer]

Hi there,

I have isolated the problem into a small example
worksheet. Can I send you the worksheet so that you can
take a look at it?

It consists of a button that opens a user form:

Sub runSelectGroupsForm()
SelectGroups.Show
End Sub

The user form consists of two text boxes that have the
following underlying code:

Private Sub TextBox1_Change()
TextBox2.Value = TextBox1.Value
End Sub

Private Sub TextBox2_Change()
TextBox1.Value = TextBox2.Value
End Sub

I created the file on a PC using Windows XP and Office
2003. The Mac is running OSX and Office 2004.

I can open the worksheet, click the button which opens
the user form, but when you try to enter text into either
text box then the whole program crashes.

Any ideas?

Many thanks for any help,

Tim Archer

 

[JE McGimpsey]

Any ideas?

I'm not sure why it works in WinXL, but the reason it crashes in MacXL
is that you've created an infinite loop. The crash occurs when XL runs
out of stack space.

When you make a change (e.g., enter a character) in Textbox1, the
Textbox1_Change() event fires, assigning the value in Textbox1 to
Textbox2.

However, *that* causes the Textbox2_Change() event to fire, assigning
the value in Textbox2 to Textbox1.

That in turn causes Textbox1_Change() to fire again, and the textboxes
continue poking at each other until XL decides to send them both to bed
without supper.

In WinXL03, the one event fires the other, but then the second doesn't
fire the first. I'll have to investigate further, but this seems like a
bug to me (a convenient bug, but a bug nonetheless).

The way I usually handle this is to create a public boolean variable and
use it much like Application.EnableEvents = False in a regular code
module:

Private bEnableEvents As Boolean

Private Sub UserForm_Initialize()
bEnableEvents = True
End Sub

Private Sub TextBox1_Change()
If bEnableEvents Then
bEnableEvents = False
TextBox2.Value = TextBox1.Value
bEnableEvents = True
End If
End Sub

Private Sub TextBox2_Change()
If bEnableEvents Then
bEnableEvents = False
TextBox1.Value = TextBox2.Value
bEnableEvents = True
End If
End Sub

 

[Tim Archer]

That makes a lot of sense. Thanks so much for your
help. It's always something simple!