Sorry - I got interrupted before finishing that last post...

At the (after) line, I (would) see the MyLoginUserForm_Initialize() execute
because the form object variable was suddenly = Nothing, and the first
reference to a form object variable's properties/members will re-load the
UserForm - from scratch.

(ps, I'm a well-seasoned Access developer, so I do know about manipulating
and using public variables, classes, object instances & such in a VBA
environment.)

"Mark Burns" wrote:

Chip,

Your example is grossly inadequate to demonstrate the issue.
Try something more like this: (warning "air code" caveats apply)

Public myObject as myLoginUserForm

Public sub Workbook_open() 'to make it clear _when_ we're initializing a
global/public variable value here

set myObject = NEW MyLoginUserForm
myObject.show
'a bunch more stuff happens, but we never "unload myobject" we only
"myobject.hide" so that the myobject.securityid and myobject.userid values
are always available as the applicaton runs. got it? (ps. and do NOT tell me
to just "put these things into a hidden worksheet" mkay?)

end sub


'later on we have code like this...
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet

Debug.Print "befo " & CStr(myobject.uerid)
Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.TextBox.1")
OleObj.Object.Text = "Pub: " & CStr(Pub)
MsgBox "after: " & CStr(myobject.userid)

End Sub


"Chip Pearson" wrote:

What you describe is not the standard and ubiquitous behavior. For
example, the following code sets a public variable, then inserts an
ActiveX text box on the active worksheet and then displays the value
of the public variable. This shows that the contents of the variable
are preserved.


Dim Pub As Long
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet
Debug.Print "befo " & CStr(Pub)
Pub = 12345
Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.TextBox.1")
OleObj.Object.Text = "Pub: " & CStr(Pub)
MsgBox "after: " & CStr(Pub)
End Sub


As far as no warning goes, you may have the "Notify Before State Loss"
setting turned off. Also, you should have Error Trapping set to "Break
In Class Module".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 13 Mar 2009 14:14:04 -0700, Mark Burns
wrote:

Let me see if I understand this properly.

If you either add or delete an ActiveX control to a worksheet dynamically
via VBA code, you trigger an involuntary and automatic RECOMPILE of the VBA
environment, which instantly wipes out *ALL* the global variables stored in
the heap? (Oh, and WITHOUT TRIGGERING ANY WARNINGS, even for a WATCH set to
break on "any changes to the value"?!!!)

...Which would also explain why you get the "Cannot enter break mode at this
time" error rather abruptly if you hit f8 in the debugger on the line that
does something ilke this:
sheet1.oleobjects.add(msoPickAShapeConstantHere , top, left, height, width)
or this:
sheet1.oleobjects("MyShapeNameHere").delete

I have just ONE little question.
WHY THE HECK IS THIS NOT FEATURED *VERY PROMINENTLY* IN THE DOCUMENTATION
FOR THE OLEOBJECTS METHODS!!!???

This is a *SERIOUSLY limiting behavior* for anyone wanting to actually use
the capabilty to dynamically create/delete OLEObjects/ActiveX controls at
runtime!
...well, at least from a VBA application (I suppose that this wouldn't
matter too much for VSTO developers, since they don't usually have to care
about the VBA environment's heap objects very much.)

If I have this wrong in any way, could somebody please tell me?