Show/Hide Userform erases global vriables

[Dave D-C]

Question:
Why does showing and hiding a userform erase all the global variables?
This is in Excel97. Is it different in later versions?

Demo:
A module with only:
Public Globalx%

A Userform with a textbox:
Private Sub TextBox1_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = 13 Then UserForm1.Hide
End Sub

A sheet with 4 buttons:
Button1 loads Globalx with 1234.
Button2 inputs a number using a listbox.
Button3 inputs a number using a userform.
Button4 displays Globalx.

Hitting buttons 1, 2, and 4: Globalx is still valid.
Hitting buttons 1, 3, and 4: Globalx is lost.

Private Sub CommandButton1_Click()
'-Loads Globalx
CommandButton1.Caption = "Load Globalx"
CommandButton2.Caption = "Inputbox"
CommandButton3.Caption = "Userform"
CommandButton4.Caption = "Display Globalx"
DoEvents ' to see new captions
Globalx = 1234
MsgBox "globalx = " & Globalx
End Sub

Private Sub CommandButton2_Click()
'-Inputs a number using inputbox
Cells(2, 1).Value = _
InputBox("Enter a number", , 4321)
MsgBox "globalx = " & Globalx
End Sub

Private Sub CommandButton3_Click()
'-Inputs a number using userform
UserForm1.Show
Cells(3, 1).Value = _
UserForm1.TextBox1.Value
MsgBox "globalx = " & Globalx
End Sub

Private Sub CommandButton4_Click()
'-Displays Globalx
MsgBox "globalx = " & Globalx
End Sub

 

[Dave Peterson]

First, I tested with xl2003 and it worked fine for me. Globalx = 1234 for all
the message boxes.

Are you sure you didn't click the Reset button in the VBE to stop any of the
code?

Do you have any "End's" in your code (not "end if", "end sub"). The End will
reset all variables.

Maybe someone with xl97 will chime in.

 

[Leith Ross]

Hello Dave,

This is normal for Excel '97. I encountered the same annoying feature
few years back. The work around I came up with was to transform th
UserForm into a Window so it could be minimized an retain the values
The module contains VBA wrappers for the API to make it easy to use
The code also enables you to add an icon to the UserForm. The code i
too long to include here. If you would like it, email m
(e-mail address removed) and I'll send it to you.

Sincerely,
Leith Ros

 

[Dave D-C]

First, I tested with xl2003 and it worked fine for me ..

This is normal for Excel '97. I encountered the same annoying feature a
few years back. The work around I came up with was ..
.. If you would like it, email me

Thanks guys. What a great newsgroup! (Am emailing Leith) Dave C