Hi all ,
My first message here
I am struggling to make the combo box I am using for selecting employees in my timetable disappear after selection.
It disappears only after clicking on another cell.
Is it possible to make it disapear after the click in the dropdown menu?
Another problem is if I click on the line between 2 cells the cursor makes a jump up or down , so you have to carefully select the cell .
I am a total beginner and I copied the vba code from a tutorial so I am unable to understand how I could fix the code
I tried to use the mini sheet feature but if I try to copy my sheet I receive the error: 1004 run-time referred to Set cboTemp = ws.OLEObjects("TempCombo")
this is the code I am using
My first message here
I am struggling to make the combo box I am using for selecting employees in my timetable disappear after selection.
It disappears only after clicking on another cell.
Is it possible to make it disapear after the click in the dropdown menu?
Another problem is if I click on the line between 2 cells the cursor makes a jump up or down , so you have to carefully select the cell .
I am a total beginner and I copied the vba code from a tutorial so I am unable to understand how I could fix the code
I tried to use the mini sheet feature but if I try to copy my sheet I receive the error: 1004 run-time referred to Set cboTemp = ws.OLEObjects("TempCombo")
this is the code I am using
VBA Code:
'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler
If Tgt.Validation.Type = 3 Then
Cancel = True
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Tgt.Validation.Type = 3 Then
Application.EnableEvents = False
str = Tgt.Validation.Formula1
str = Right(str, Len(str) - 2)
With cboTemp
.Visible = True
.Left = Tgt.Left
.Top = Tgt.Top
.Width = Target.Width + 5
.Height = Target.Height + 4
.ListFillRange = ws.Range(str).Address
.LinkedCell = Tgt.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
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
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
'Table with numbers for other keys such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx
Private Sub TempCombo_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
'====================================
