Chip,

First, thank You for taking the time to respond. I sincerely appreciate your
insights.

Yes, my original post was a rant, as i had just wasted many hours figuring
out what the heck was happening to my public variable values. (and I was
rather shocked and ticked about it once I read the explanation you will see
below). I traced the disapperance of the values down to the line where I
inserted a new checkbox into a form.
Then I went looking for an explanation, because I figured that I couldn't
possibly have been the first person to notice this happening.
I found this:
http://support.microsoft.com/kb/231089
then I found this:
http://www.experts-exchange.com/Soft..._23574623.html
(note the answer down at the bottom which I'll quote in case the link
doesn't show it to you - weird site that way)

Answer: "rorya:When you add OLEobjects to a worksheet, the OLEobject becomes
a property of the worksheet (which is why you can use Me.Combobox1 etc in a
worksheet code module) so the project recompiles. This resets your global
variables. If possible use Forms objects instead."

After reading that is when I got annoyed enough to rant here.
So, sorry about the tone of my initial message, but I did try and do my
homework first.

You captured the spirit of what i'm doing here.
The only other factors that might be relevant to this discussion is for me
to add that the vba code is running from a tempate file, (which shouldn't
make any difference here, should it? ...and if it does, then my rant still
applies), and the control i'm trying to add dynamically is a checkbox that is
NOT bound to a cell reference.

I will try and boil down the smallest possible example so that I can
reliable reproduce the behavior I am seeing with the actual code I'm using.
It will probably not be until Monday that I post that.
Oh, and BTW, Excel 2003 Sp3 is the flavor involved here.

Thanks again for taking the time to respond, Chip.

"Chip Pearson" wrote:

Your example is grossly inadequate to demonstrate the issue.

Given that your original post was little more than a rant and rave, I
chose a simple example to illustrate that what you wrote was not
necessarily the case.

Try something more like this: (warning "air code" caveats apply)

If you're trying to make a point, don't use "air code" and assert
caveats. Write real, compilable code that works and substantiates your
assertions.

Assume you have a UserForm named UserForm1 that contains the following
code:

Public Prop1 As String
Public Prop2 As String
Private Sub btnClose_Click()
Me.Hide
End Sub

Then, in Module1, use

Public UForm As UserForm1
Public ModValue As String

Sub AAA()
Dim WS As Worksheet
Dim TBX As OLEObject
Set UForm = New UserForm1
Set WS = ActiveSheet
UForm.Prop1 = "hello"
UForm.Prop2 = "world"
ModValue = "abcd"
Debug.Print "Before Show:", UForm.Prop1, UForm.Prop2, ModValue
UForm.Show
Debug.Print "After Show:", UForm.Prop1, UForm.Prop2, ModValue
Debug.Print "Creating TextBox..."
Set TBX = WS.OLEObjects.Add("Forms.TextBox.1")
TBX.Object.Text = "Text Box"
Debug.Print "After TextBox", UForm.Prop1, UForm.Prop2, ModValue
End Sub


And in Module2 use

Sub BBB()
Debug.Print "BBB: ", UForm.Prop1, UForm.Prop2, ModValue
End Sub

There are two Public variables, one a simple string and the other an
reference to a UserForm. The code sets the string and instantiates
UserForm1 into UForm. UForm's public variables are set and the form is
shown modally. When the form is then hidden by btnClose, the variables
are printed, and this shows that they retained their values. Then, a
Forms.TextBox.1 ActiveX is created and the Public variables are
printed again and again they retained their values. Once AAA ends, you
can run BBB and see that the public variables retained their values
after the form is shown, after the ActiveX is created, and after the
first sub ends.

If you close the UserForm by clicking the "X" in the upper right
corner of the form, you are unloading the form, so the form's
properties revert to strings, and you'll get an error 91 in BBB.

If you have some real, compilable, code that shows different behavior,
by all means post it. But the bottom line is that, contrary to your
original post, creating an ActiveX control doesn't (necessarily) dump
global variables.

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