Combo box macro problems

[jasonbates]

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

 

[Tom Ogilvy]

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.

 

[jasonbates]

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]

Debra Dalgleish is the author/owner of the Contextures site.

You said you were using VBA. That URL is about one of the .NET languages
which do have garbage collection.

Has nothing to do with her code or what you are doing. The most common
cause of event code stopping is because events are disabled with the

Application.DisableEvents = False

command and never getting to the

Application.EnableEvents = True

command.

Debra's code assumes you have the VBE set to Break on Unhandled Errors set
in Tools=>Options, General tab. If you don't, then you need to change to
that setting.

If you are disabling events somewhere else with code that you added or
already had, then you need to look there.

 

[Tom Ogilvy]

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.

 

[jasonbates]

Thanks for the help!

I'll delve into the other macro's on the sheet (written by other
people) to see if something funky is happening with them. I think that
there is something that does some kind of global protect / unprotect.

Thanks again for your pointer away from the wrong direction, I do
appreciate it.

Regards,

JB>

 

[jasonbates]

Thanks for your help Tom pointing me in the right direction... or at
least away from the wrong direction.

It turned out that an udpate macro protected the sheet with
drawingobjects:= true. So the pop-up combo box wasn't being drawn. (who
knew that a combo box was a drawing object!? ... well you guys probably
did, but anyway, changing that protect statement to equal false.. cured
all my woes.

Thanks for the pointers,

regards,

JB>

 

[J4Ysc3n3]

I am using that same code, and I am getting a run time error stating:

method 'OLEObjects' of object '_worksheet' failed

I emailed debra once and asked if it was the code that was wrong, but I had
named my combo box wrong. I fixed that, but it is still not working.

Any thoughts...

 

[Tom Ogilvy]

The code is pretty reliable. You saw in this thread the problem was due to
an environmental setting.

I would read Debra's page again and make sure I did everything as she
prescribes it. Then if you still have problems, perhaps start a new thread,
posting the code *you* are actually using and a reference to Debra's site
(not everyone is familar), where you have the code, and any other pertinent
details.

One thing to do preliminary to starting a new thread is to open a blank
workbook and put Debra's code in there and get it working (apply any
conditions specific to the macro, such as data validation in a few cells as
I recall). This will give you some confidence that it works. Then see how
your actual workbook is different.