<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > <bg...@yahoo.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> > > 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.
> >
> On 26 Juli, 18:57, "Peter T" <peter_t@discussions> wrote:
> > 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
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
|
Similar Threads
