Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
I am trying to create a macro-and I am new at this so bare with me-
where if I am within a cell by simply pushing the up or down arrow the value within the cell will increase or decrease with each key stroke. Can this be done. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
If by "within a cell" you mean in Edit Mode, then no - macros are
disabled in Edit mode. If you want this to happen in one particular cell, this may work for you: Put this in the worksheet code module: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Application.OnKey "{UP}" Application.OnKey "{DOWN}" With Target If .Count = 1 Then If Not Intersect(.Cells, Range("J5")) Is Nothing Then Application.OnKey "{UP}", "AddOne" Application.OnKey "{DOWN}", "SubtractOne" End If End If End With End Sub (change J5 to suit). Put these in a regular code module: Public Sub AddOne() With ActiveCell If IsNumeric(.Value) Then .Value = .Value + 1 End With End Sub Public Sub SubtractOne() With ActiveCell If IsNumeric(.Value) Then .Value = .Value - 1 End With End Sub In article .com, wrote: I am trying to create a macro-and I am new at this so bare with me- where if I am within a cell by simply pushing the up or down arrow the value within the cell will increase or decrease with each key stroke. Can this be done. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Run the first macro to add a button to the Worksheet Menu Bar that toggles
the behavior of the arrow keys. This button will be temporary and so will automatically delete upon closing Excel. After adding the button click it and then test the arrow keys. Click it again to reset to normal. Sub AddBtn() With Application.CommandBars(1) With .Controls.Add(Temporary:=True) .OnAction = "TogArrowKeys" .FaceId = 468 End With End With End Sub Sub TogArrowKeys() Dim btn As CommandBarButton With Application Set btn = .CommandBars.ActionControl If btn.State = msoButtonUp Then .OnKey "{UP}", "IncrementCell" .OnKey "{DOWN}", "DecrementCell" btn.State = msoButtonDown Else .OnKey "{UP}" .OnKey "{DOWN}" btn.State = msoButtonUp End If End With End Sub Sub IncrementCell() With ActiveCell If Not IsNumeric(.Value) Then Exit Sub .Value = .Value + 1 End With On Error GoTo 0 End Sub Sub DecrementCell() With ActiveCell If Not IsNumeric(.Value) Then Exit Sub .Value = .Value - 1 End With End Sub Regards, Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
You can remove the "On Error GoTo 0" line in the IncrementCell macro. A
leftover when I changed my mind on how to deal with the potential of text in the cells. Greg "Greg Wilson" wrote: Run the first macro to add a button to the Worksheet Menu Bar that toggles the behavior of the arrow keys. This button will be temporary and so will automatically delete upon closing Excel. After adding the button click it and then test the arrow keys. Click it again to reset to normal. Sub AddBtn() With Application.CommandBars(1) With .Controls.Add(Temporary:=True) .OnAction = "TogArrowKeys" .FaceId = 468 End With End With End Sub Sub TogArrowKeys() Dim btn As CommandBarButton With Application Set btn = .CommandBars.ActionControl If btn.State = msoButtonUp Then .OnKey "{UP}", "IncrementCell" .OnKey "{DOWN}", "DecrementCell" btn.State = msoButtonDown Else .OnKey "{UP}" .OnKey "{DOWN}" btn.State = msoButtonUp End If End With End Sub Sub IncrementCell() With ActiveCell If Not IsNumeric(.Value) Then Exit Sub .Value = .Value + 1 End With On Error GoTo 0 End Sub Sub DecrementCell() With ActiveCell If Not IsNumeric(.Value) Then Exit Sub .Value = .Value - 1 End With End Sub Regards, Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |