Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box keyboard shortcut
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box keyboard shortcut
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. -- Regards, Tom Ogilvy "davy_gravy" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box keyboard shortcut
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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "davy_gravy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keyboard Shortcut | Excel Discussion (Misc queries) | |||
Keyboard shortcut | Excel Discussion (Misc queries) | |||
Keyboard shortcut for name box | Excel Discussion (Misc queries) | |||
Keyboard Shortcut | Excel Discussion (Misc queries) | |||
Keyboard shortcut for Best Fit | Excel Discussion (Misc queries) |