Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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. -- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 Tom Ogilvy wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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 Tom Ogilvy wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box macro problems
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. -- Regards, Tom Ogilvy "J4Ysc3n3" wrote in message ... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo problems | Excel Discussion (Misc queries) | |||
Problems with Combo Box | Excel Programming | |||
Combo Box List Problems | Excel Discussion (Misc queries) | |||
Combo Box Problems | Excel Programming | |||
Problems with combo boxes | Excel Programming |