also, Your URL goes to group:

microsoft.public.vsnet.vstools.office

That is for .NET languages and the Visual Studio Tools for Office (also for
..NET). VBA is based on VB6, the language replaced by the .Net languages,
so only rudimentary commonality. Looking in that group won't get you much
help/insight on VBA.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Thanks for your reply...

The code is from a great site called www.contextures.com, as I
inherited a spreadsheet this week, and I'm four days into my VBA
programming experience, I am trying to reuse all the code that I can.
This code snippet was a great find... Thanks Contextures!

With regards to garbage collection, I've been lead to believe that
there is a common problem in excel which leads to some event handlers
not working after a period of time due to declaring variables in the
method rather than at the class level. Apparently such method based
objects are cleared up by the garbage collector when the method goes
out of scope. Is this incorrect?

There were quite a few posts on the subject I saw... for instance:

http://tinyurl.com/qek6d

There are quite a few similar posts about methods stopping working
suddenly in that newsgroup... many of them attributed to method vs.
class issues. I thought that this might be my problem too.

Regards,

Jason


Tom Ogilvy wrote:
Looks like code from Debra Dalgleish's site.

In any event, I would not see there being a command that would solve your
problem as I would not see this code causing that problem. VB6/VBA does
not
do garbage collection per se. It does reference counting for objects.
However, you never create or delete an object - you just move a single
object
around.

I would suggest that Debra's code has been used quite a bit and if it had
a
systemic problem it would have surface frequently. I haven't seen such
posts, however.

--
Regards,
Tom Ogilvy


" wrote:

Guys,

I've been using a pretty useful worksheet macro that creates combo
boxes
to replace list boxes for selection and validation when you double
click on a cell...
(it's useful because I can create wider, longer lists, different fonts,
and works on all of the validation drop downs on the spreadsheet).
Unfortunately after some heavy calculation macro's the double click
event seems to stop working

From what I've read in this forum I think that the problem may be to
do
with with Garbage collection.

My question... what is the simplest code I need to add to my excel
workbook to stop this from happening... can I just define the combo box
object somewhere globally?

I'm a neohpyte with excel VBA so please be gentle..... Thanks in
advance

JB

--- the code that resides in the sheet ---

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")

On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With

On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

* * * *

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub