combobox only appears by double clicking

June 5, 2011 at 06:31:08
Specs: Windows XP
I have a combobox linked to a data validation list. i have the macro to make the combobox appear in a cell with the data validation dropdown in it when i double click the cell. can you make the combo box appear without having to double click the cell?


See More: combobox only appears by double clicking

Report •

#1
June 7, 2011 at 03:33:04
Can you show us some of your code? also explain in a bit more detail what you are doing and how you have setup your spreadsheet, this hsould give us more information and will help us with your problem.

Report •

#2
June 7, 2011 at 04:25:55
Here is the code. I work in a Food Service establishment and I have set up a workbook for my production paperwork. The first page is a menu diagram with cells linked to a data validation list, on the same page, of recipe names. I have entered a combobox that automatically links to the cell that contains the data validation list dropdown when I double click the cell. I want it to automatically appear when I tab from cell to cell. Some of the cells contain formulas, but so far they are not affected.


Private Sub Work() '==========================
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("Recipes")
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("Recipes")
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
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub Recipes_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================



Report •
Related Solutions


Ask Question