Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In edit mode and key event on sheet? (Excel2003 SP2)

    Like this maybe (normal module!!):

    <pre>Sub StartCatchF2()
    Application.OnKey "{F2}", "CatchF2"
    End Sub

    Sub CatchF2()
    MsgBox "Going in edit mode!"
    Application.OnKey "{F2}"
    SendKeys "{F2}"
    Application.OnTime Now, "StartCatchF2"
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    New Lounger
    Join Date
    Oct 2007
    Location
    Copenhagen, Denmark
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    In edit mode and key event on sheet? (Excel2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hi my friends!

    I have a simple one here: Is it possible to monitor key-events on a sheet? Say I want to monitor F2 on a cell (going into edit-mode with a cell). I want to be able to tell if the user did press a certain key like F2 in this case. And the reason why I want this is that, at least to my understanding, it is impossible to determine at a certain point in time if of if not Excel is in edit mode.

    I have though found a workaround (here) that uses the fact that the new-menuiten are dimmed in the edit case. Ugly yes; but working in most cases.

    Up until now what I did was to monitor BeforeDoubleclick on sheet-level and in that case setting a boolean TRUE. The reverse thing on SheetChange. That is:

    Private Sub ExcelWorkbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
    Debug.Print "Cell(" + CStr(ActiveCell.Row) + ", " + CStr(ActiveCell.Column) + ") is in edit mode "
    bApplicationStateIsInEditMode = True
    End Sub

    Private Sub ExcelWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Debug.Print "SheetChange"
    bApplicationStateIsInEditMode = False
    End Sub

    That strategy only works well if I include keys as well - can that be done?

    Kind regards,
    Michael Mogensen, Denmark

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: In edit mode and key event on sheet? (Excel2003 SP2)

    I am not sure exactly what you are after. The <F2> button or dbl-clicking a cell are just 2 ways of getting into "Edit Mode" (and they are both separate events that may lead to getting into edit mode). Typing in the formula bar or just typing in cell are a couple other ways.

    Are you trying to monitor every way to get into edit mode. i don't understand the reason you need to do this. What is the goal of the exercise? Perhaps there are alternatives to do what you need.

    Steve

  4. #4
    New Lounger
    Join Date
    Oct 2007
    Location
    Copenhagen, Denmark
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why do I want this?

    Hi Steve!

    The reason why I want to do this is this: We have a system that, among other office-parts, deals with Excel-sheets. The system (system here beeing a main app. in VB working on a dark-horse-callback-mechanism to do stuff with our various office-parts) performs an AutoSave after a certain timeout period to be able to not only save the sheet itself but to (auto)save our businessobject in some Oracle tabels below. Now this disturbing fact: When the main-app performs the save torwards Excel the state the user are in in his sheet is not fully recoverable after the save!!! ... like if he is writing something in a cell he looses his input-focus-state and *overwrites* what he is dooing if he dos not look up (focus is changed to cell itself). Next, and this is even worse - part of the Excel object-model disables in that situation and the saving mechanism can not rely on methods - promising methods like:

    Private Function IsExcelInEditMode() As Boolean
    Dim NewItem As Variant
    Set NewItem = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30002).Controls.Item(1)
    IsExcelInEditMode = (Not NewItem.Enabled)
    End Function

    Dos always answer the same no matter if its in or not in F2-mode.

    That's our problem! So in few words this is about beeing able to restore the STATE after the save is done!

    Now my first attempt to fix this was to deal with SheetBeforeDoubleClick and SheetChange and following in each method update a bApplicationStateIsInEditMode boolean to keep track of the state *before* it goes in to it and *after* it leaves it. This works pretty fine but is not 100%. If not using dbl.click this dos not work! Next attempt was to use the method IsExcelInEditMode(...) above (which test the New-item enable-state) - also failure here for the reasons I mentioned above.

    3. try was to improve the first approch - extending to developing a "F2-monitoring-mechanism" if possible. It seems that this dos not work outside Excel (no macros must reside in any of our sheets - all code must be in VB main app.).

    So bottom line is that for now I'm back to level zero or 0.1! Too bad ... :-(

    I don't think this can be solved - I've found evidence on the internet that people elsewhere are facing the same bizare Excel behaviour and can't fix it - or maybe I'm just too stupid to think of it.



    Kind regards,
    Michael Mogensen

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why do I want this?

    This appear to work for me (VB6):

    msgbox XLApp.CommandBars("Worksheet Menu Bar").FindControl(id:=18, recursive:=True).Enabled
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Oct 2007
    Location
    Copenhagen, Denmark
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why do I want this?

    Hi Jan!

    Thanx for the input first of all!

    I've tested it like this:

    Private Function IsExcelInEditMode() As Boolean
    IsExcelInEditMode = (Not Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=18, recursive:=True).Enabled)
    End Function

    Private Sub ExcelWorkbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
    Debug.Print ("Am I in edit mode: " + IIf(IsExcelInEditMode(), "Y", "N"))
    End Sub

    Private Sub ExcelWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Debug.Print ("Am I in edit mode: " + IIf(IsExcelInEditMode(), "Y", "N"))
    End Sub

    So I expect that my dbl. click handler gives me 'Y' and my change-handler gives me 'N' - sorry, but this always gives me an 'N'.

    So in other words it seems that Excel disables some of it's functionality in certain situations or what conclusion can we possible reach do you think?

    Thanks in advance,

    Kind regards,
    Michael Mogensen

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Why do I want this?

    The key is in the event name: SheetBeforeDoubleClick
    At the time the event fires, Excel has not yet entered Edit mode. The problem is not the function, it's that you can't use it with that event, but then you shouldn't need to, since it's a real-time test.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Oct 2007
    Location
    Copenhagen, Denmark
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why do I want this?

    Hi rory!

    Thanx' for your input. OK - if I understand i right the situation is that I'm not allowed to call the function in all situations(?) That's sad... I mean here it's a "background-reason" (=AutoSave) that triggers the call and I can not know what STATE the user are in - therefore I can't know if it's ok or not to call IsExcelInEditMode(...) and get the STATE info. for the save-handling rutine behind. I get the feeling of circular conclusion here btw...?

    So bottom line is that I can NOT do this and following restore the state - right?

    Kind regards,
    Michae Mogensen

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Why do I want this?

    Rory's point was that when you are checking for edit using the before dbl-click event, the "N" is accurate. You are checking it before it is in Edit mode. You need to run your function after it gets into edit mode. It is not a matter of when it is "allowed" but the timing of when it is checked.

    The BeforeDbl-click event is triggered after the dbl-click but before the event (before it gets into edit mode). Excel goes in edit mode after it leaves that code (unless the "goto edit state" is canceled in the code)

    To test the function you will have to call it with a procedure while XL is in edit mode. I don't think this can be done from excel but would have to be from a different program outside of excel.

    Steve

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Why do I want this?

    You can call the function any time you like. My point is that if you call the function in the BeforeDoubleClick event, it returns False because the application is not (yet) in Edit mode. The function itself works perfectly and if you call it from an external routine, it should tell you the current state of the application.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    New Lounger
    Join Date
    Oct 2007
    Location
    Copenhagen, Denmark
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why do I want this?

    I think I understand - *think*

    I fooled myself into a wrong conclusion because did'nt know the nature of the handler! If, on the other hand you are right that I can rely on its answer from a call from an external source my problem are solved.

    I hand over two Duff beers in advance.

    Kind regards,
    Michael Mogensen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •