Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box Code
I have created a combo box, and validated certain cells with a drop down
list. I want to be able to double click the validated cells to access the combo box. I set up the combo box, and defined a dynamic range, then validated the cells I want with a list. The drop down windo works, but it's not the combo box with the auto complete, font, and size options I chose. I then used this cobe in the VB editor window to tell excel to hide the combo box and show it when I double click a validated cell. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.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 'clear and hide the combo box .ListFillRange = "" .LinkedCell = "" .Visible = False End With On Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains a data validation list Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .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 When I go back to my worksheet and try to double click one of my validated cells I get an error message that says: method 'OLEObjects'-worksheet' failed I'm not sure how to debug this, any suggestions would help.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box code | Excel Programming | |||
code for combo box | Excel Programming | |||
Combo Box Code... | Excel Programming | |||
Combo Box Code... | Excel Programming | |||
Combo Box code | Excel Programming |