ActiveSheet.OLEObjects.Add resets globals to Nothing

[bg_ie]

Hi,

I have the following code -

' Sheet 1
Dim temp As Klass1

Private Sub CommandButtonCreateObject_Click()
Set temp = New Klass1
End Sub

Private Sub CommandButtonTest_Click()
Dim myButton As New OLEObject
Dim CurSheet As Worksheet

Cells(1, 1) = temp.GetTemp
Set CurSheet = Worksheets("Sheet1")
Set myButton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
Cells(1, 2) = temp.GetTemp
End Sub

'Klass1
Private tmp1 As Integer

Sub Class_Initialize()
tmp1 = 10
End Sub

Function GetTemp() As Integer
GetTemp = tmp1
End Function

I have 2 button on my worksheet in order to trigger
CommandButtonTest_Click() and Class_Initialize() respectfully.

So first I press the CreateObject button first and then I should be
able to press Test button multiple times. The problem though is that
when I press the Test button the 2nd time, I get an error. This is
because temp has been reset to Nothing. Why might this happen? Also,
the number 10 is being printed to both cells so it seems that temp is
set to nothing at the end of the CommandButtonTest_Click() function.
Commenting out the line -

Set myButton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")

causes everything to work fine except for the button not being
created. When I place a breakpoint error on this line, I get a error
stating that the breakpoint cannot be used just now (this error is
given in Swedish so I can't give you the english version...), with the
option to terminate or continue. Either way temp is being reset each
time CommandButtonTest_Click() is called and
ActiveSheet.OLEObjects.Add seems to be the culprit.

Any ideas?

Thanks,

Barry.

 

[Peter T]

Hi Barry,

Adding worksheet controls appears to re-compile the project once the code
completes. Amongst other things global variables are destroyed but can get
much worse with total crash if trying, say, to attach new withevents code to
the new controls.

If possible, have code to add new controls to another workbook, ie not to
ThisWorkbook. Even that could destroy globals in the other wb.

Regards,
Peter T

 

[bg_ie]

Hi Barry,

Adding worksheet controls appears to re-compile the project once the code
completes. Amongst other things global variables are destroyed but can get
much worse with total crash if trying, say, to attach new withevents code to
the new controls.

If possible, have code to add new controls to another workbook, ie not to
ThisWorkbook. Even that could destroy globals in the other wb.

Regards,
Peter T

Thanks for the reply, it make a lot of sense. When you say use a
second workbook, do you mean that two workbooks would need to be open
at the same time? It seems I'd probably be better off skipping the
idea of adding buttons to my columns and perhaps just use a single
button instead for saving data associated with each of my columns...

/Barry

 

[Peter T]

Thanks for the reply, it make a lot of sense. When you say use a
second workbook, do you mean that two workbooks would need to be open
at the same time? It seems I'd probably be better off skipping the
idea of adding buttons to my columns and perhaps just use a single
button instead for saving data associated with each of my columns...

/Barry

Concerning the two workbooks approach, code in wb-A to add new controls into
wb-B. So yes, the two wb's would need to be open at the same time. This
avoids loss of global and static variables in wb-A, though not in wb-B.

As I mentioned, apart from adding the controls you will also want to link
code to trap their events. This is where much more serious problems can
occur if all is being done with the same wb.

Maybe Forms controls might be viable for your needs, these are easy to add
and 'OnAction' code can be pre-installed. Assign relevant names, in the
OnAction macro start with something like -
sCaller = Application.Caller
Select Case sCaller

Regards,
Peter T