omek07
04-05-2016, 07:49 PM
Hello,
I am new in the forum and in the excel vba code
I would like to ask if anyone knows how to exit from the compobox menu after we select something (after we click to the item that we want from the dropdown menu)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
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 Resume Next
ActiveWorkbook.Names("DVList").Delete
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula into a Name:
ActiveWorkbook.Names.Add Name:="DVList", RefersTo:=Target.Validation.Formula1
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 = "=DVList"
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
With Me.TempCombo
' .Value = "-"
End With
errHandler:
Application.EnableEvents = True
Exit Sub
End If
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
Application.EnableEvents = False
Application.ScreenUpdating = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
'=========================================
Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 1 'Lclick
ActiveCell.Offset(1, 0).Activate
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
I found this code and it worked very good but now i want when i select
something to close the dropdown menu without press any button or click
to another cell.
I have tried with this 2 codes below and , its working when i select something but when i
press a letter to find something from my list it crush the excel
Private Sub TempCombo_Change()
ActiveCell.Select
End Sub
and
Private Sub TempCombo_Click()
ActiveCell.Offset(0, 1).Activate
End Sub
I've added a sample of my worksheet
My lists is in the Equipment sheet. The A column is my Big list , the B column is the Descriptions , the C, D, E columns is for the Unused items (i used some formulas so i can have the Unused items and with this way the user cant select the same item again). the E column (unused2 list) is the Column that i use for the Data Validation and for the Tempcombo.
My main Sheet is the Daily_Dispatch sheet . The Validation data and the Tempcombo is in the A column under of the Equip.
You can open the tempcombo with double click
if you select a list item with mouse click is fine but if you try to type something (lets say SC008) its gonna crush the Excel.
If you erase this
Private Sub TempCombo_Click()
ActiveCell.Offset(0, 1).Activate
End Sub
You will not have the crush BUT if you select something from the drop down menu it highlightgs the next item from the list (which is a little bit confusing) but if after you select the item and press ENTER it gonna give you the correct ITEM.
Is anyone that can help me with this
Thank you very much in advanced
I am new in the forum and in the excel vba code
I would like to ask if anyone knows how to exit from the compobox menu after we select something (after we click to the item that we want from the dropdown menu)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
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 Resume Next
ActiveWorkbook.Names("DVList").Delete
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula into a Name:
ActiveWorkbook.Names.Add Name:="DVList", RefersTo:=Target.Validation.Formula1
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 = "=DVList"
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
With Me.TempCombo
' .Value = "-"
End With
errHandler:
Application.EnableEvents = True
Exit Sub
End If
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
Application.EnableEvents = False
Application.ScreenUpdating = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
'=========================================
Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 1 'Lclick
ActiveCell.Offset(1, 0).Activate
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
I found this code and it worked very good but now i want when i select
something to close the dropdown menu without press any button or click
to another cell.
I have tried with this 2 codes below and , its working when i select something but when i
press a letter to find something from my list it crush the excel
Private Sub TempCombo_Change()
ActiveCell.Select
End Sub
and
Private Sub TempCombo_Click()
ActiveCell.Offset(0, 1).Activate
End Sub
I've added a sample of my worksheet
My lists is in the Equipment sheet. The A column is my Big list , the B column is the Descriptions , the C, D, E columns is for the Unused items (i used some formulas so i can have the Unused items and with this way the user cant select the same item again). the E column (unused2 list) is the Column that i use for the Data Validation and for the Tempcombo.
My main Sheet is the Daily_Dispatch sheet . The Validation data and the Tempcombo is in the A column under of the Equip.
You can open the tempcombo with double click
if you select a list item with mouse click is fine but if you try to type something (lets say SC008) its gonna crush the Excel.
If you erase this
Private Sub TempCombo_Click()
ActiveCell.Offset(0, 1).Activate
End Sub
You will not have the crush BUT if you select something from the drop down menu it highlightgs the next item from the list (which is a little bit confusing) but if after you select the item and press ENTER it gonna give you the correct ITEM.
Is anyone that can help me with this
Thank you very much in advanced