N
news.microsoft.com
Hi microsoft.public.excel.programming newsgroup, this is my first time here.
I'm developing an event driving application in Excel, mostly controlled by
the keyboard, namely ALT+(cursor keys), and I have this function on Module1
of a .xls:
Public Function gini()
MsgBox "do instructions here"
gini = Replace(Application.Caller.Formula, "=", ".")
End Function
and on ThisWorkbook I have the following event:
Public Sub Workbook_Open()
Application.OnKey "%{RIGHT}", "parseOnce"
Application.OnKey "%{DOWN}", "parseForever"
End Sub
the parseOnce() is as follows:
Public Function parseOnce(Optional cell As Range)
Set iPtr = IIf(cell Is Nothing, ActiveCell, cell)
hasParsed = False
If iPtr.HasFormula = True Then
iPtr.Calculate
hasParsed = True
End If
parseOnce = hasParsed
End Function
iPtr (shorthand for instruction pointer) is a Range type variable either
containing the ActiveCell of the cell passed as an argument to parseOnce().
Issuing iPtr.Calculate does exactly what I want, it displays the message "do
instructions here". However, pressing Enter after entering the =gini()
formula does the same thing, and that I don't want.
My question is, how can I execute (or calculate?) a formula/function on
pressing ALT+(right-arrow), without executing it when it is entered on the
cell ? When using Excel's shortcut Alt+Shift+Ctrl+F9 (Re-calculate all),
any of these functions shouldn't run either, they should only run with the
ALT+key combination. Any other Excel formula, for example =SUM() should run
when entered, or re-calculated.
I hope you can help me and thank you for reading so far, sincere regards,
Marco
I'm developing an event driving application in Excel, mostly controlled by
the keyboard, namely ALT+(cursor keys), and I have this function on Module1
of a .xls:
Public Function gini()
MsgBox "do instructions here"
gini = Replace(Application.Caller.Formula, "=", ".")
End Function
and on ThisWorkbook I have the following event:
Public Sub Workbook_Open()
Application.OnKey "%{RIGHT}", "parseOnce"
Application.OnKey "%{DOWN}", "parseForever"
End Sub
the parseOnce() is as follows:
Public Function parseOnce(Optional cell As Range)
Set iPtr = IIf(cell Is Nothing, ActiveCell, cell)
hasParsed = False
If iPtr.HasFormula = True Then
iPtr.Calculate
hasParsed = True
End If
parseOnce = hasParsed
End Function
iPtr (shorthand for instruction pointer) is a Range type variable either
containing the ActiveCell of the cell passed as an argument to parseOnce().
Issuing iPtr.Calculate does exactly what I want, it displays the message "do
instructions here". However, pressing Enter after entering the =gini()
formula does the same thing, and that I don't want.
My question is, how can I execute (or calculate?) a formula/function on
pressing ALT+(right-arrow), without executing it when it is entered on the
cell ? When using Excel's shortcut Alt+Shift+Ctrl+F9 (Re-calculate all),
any of these functions shouldn't run either, they should only run with the
ALT+key combination. Any other Excel formula, for example =SUM() should run
when entered, or re-calculated.
I hope you can help me and thank you for reading so far, sincere regards,
Marco