Combo box keyboard shortcut

[davy_gravy]

hello again,

I'm still trying to tune up my combo box, and as I test it, I find
things that need some work to make it smooth. I would like to have it
setup so that the mouse is not required for data entry. It can be used
as an option, just not a required tool.

How do I add a shortcut key to the following code: (This code is in
sheet1)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim ws As Worksheet
Set ws = ActiveSheet

Dim cboTemp As OLEObject
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Column = 2 And ActiveSheet.Name = "Timesheet Entry"
Then
Application.EnableEvents = False
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 275
.Height = Target.Height + 5
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

I've added this line:

Keyboard Shortcut: Ctrl Shift + Z

Without success. I've written a sub to call the doubleclick sub and
attached a shortcut there, to no avail.

If anyone can help, I'd really appreciate it.

Thanks,

Dave

 

[Guest]

in a general module

Public Sub Gravy_code()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim cboTemp As OLEObject
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Column = 2 And ActiveSheet.Name = "Timesheet Entry"
Then
Application.EnableEvents = False
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 275
.Height = Target.Height + 5
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Now
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Call Gravy_code()
End Sub

in Excel, go to tools=>Macro=>Macros and select
Gravy_code, click options and assign a short cut.

 

[davy_gravy]

Hi Tom,

Awesome! I only had to define 'cancel' & 'target' and set target =
activecell, and it worked perfectly.

I've attached the working code below for anyone else with a similar
issue:

In module 1:

Public Sub Combo_box()

'Keyboard Shortcut: Ctrl Shift + Z

Dim cancel As Boolean
Dim target As Range
Set target = ActiveCell

Dim ws As Worksheet
Set ws = ActiveSheet

Dim cboTemp As OLEObject
cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If target.Column = 2 And ActiveSheet.Name = "Timesheet Entry"
Then
Application.EnableEvents = False
With cboTemp
.Visible = True
.Left = target.Left
.Top = target.Top
.Width = target.Width + 275
.Height = target.Height + 5
.LinkedCell = target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

In sheet1's VBE:


Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, cancel
As Boolean)

Call Combo_box

End Sub


Enjoy!

thanks again Tom,

Dave