PC Review


Reply
Thread Tools Rate Thread

Combo Box Code

 
 
=?Utf-8?B?SjRZc2MzbjM=?=
Guest
Posts: n/a
 
      12th Oct 2006
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo Code Help Please golfinray Microsoft Access 3 13th Mar 2009 10:25 PM
Combo Box Code Neil Pearce Microsoft Excel Misc 2 5th Jan 2009 04:20 PM
Combo Box Code... younathan Microsoft Excel Programming 1 18th Oct 2004 08:26 PM
Getting Zip Code from Combo Box Sandra Microsoft Access Forms 5 22nd Dec 2003 03:12 PM
Combo Box Code DaveB Microsoft Access Forms 3 5th Nov 2003 09:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:43 PM.