none
Excel edit mode

Answers

All replies

  • Hi,
    The support engineer needs some additional information before he can research this for you.

    ***
    After reviewing the post, I understand that you want to write VBA code to detect if an Excel cell is in "edit mode".
     
    We cannot find related property in Excel object model. The Excel object model reference is in the following link:
     
    Could you let me know the detailed scenario in which you want to implement this functionality?
    ***

    thanks,
    -brenda (ISV Buddy Team)
    Monday, December 12, 2005 6:28 PM
  • That's for an AddIN commandBar
    As you see in Excel, when in "Edit mode", many commands are grayed. I want the same comportment.

    thanks for reply

    Denis Jaubert
    Tuesday, December 13, 2005 9:04 AM
  • Speaking of "Edit mode", could someone please tell me how do I force "Edit mode". What is the VBA replacement for in-Excel F2 key ... Is there any alternative to SendKey "{F2}" ?
    The thing is I want to alter the behavior of F2 key but I need also to enter "Edit mode" and do some other things. I've experimented with OnKey and SendKey nothing seams to work.
    Thanks in advance!
    Wednesday, December 14, 2005 8:57 AM
  • Hi Denis,

    Here's the engineer's response to your question:

    Excel does not provide an interface for outside components to detect "Edit mode" (e.g. when a cell is accepting input), though in Excel itself it can do it. In addition, when Excel is in "Edit Mode", macro will not run. Therefore, if such interface is not provided, it is not possible for an addin to detect "Edit mode".

    -brenda (ISV Buddy Team)

    Friday, December 16, 2005 5:37 PM
  • Hi Vladislav,

    If you could provide a detailed scenario our engineer will be better able to assist. Also, why do you want to force "Edit mode"? When Excel is in "Edit Mode", macros will not run.

    thanks,

    -brenda (ISV Buddy Team)

    Thursday, December 22, 2005 5:02 PM
  • Hi,
    my scenario is a bit more complicated. Let’s say I have cell A1 with formula =B1+B2 .. in B1 I have value 2 and in B2 I have value 3… cell A1 displays now 5 …if you select cell A1 and then press {F2} you enter edit mode and you see the formula … what I need is following sequence:

     {F2}+{HOME}{F9}{END} – this sequence enters edit mode, calculates the result, displays the value and moves cursor to the end of value.

     The real question is how do I re-assign this whole sequence to {F2} key and avoid it form running in infinite loop (since the key sequence contains {F2} again).

     Thanks!
    Vlado

    Friday, December 23, 2005 8:51 AM
  • Hello,
    so engineers stated that there would not be any way to disable custom commandbar controls when excel enters edit mode, right? Though excel itself is able to do that, and actually does that.

    I got such a problem when i'm in edit mode. User clicks custom toolbar button(created by COM addin). The Click event handler calls ActiveWorkbook->SaveCopyAs(newname). I get HRESULT 0x800A03EC ( 800A03EC ) error. FormatMessage(with from system constant) would not return any error message, i.e it returns blank string(not null pointer) if i'm correct.
    Wednesday, December 28, 2005 10:16 AM
  • Hi Vlado,

    here's the latest from the support engineer...

    As I mentioned, when Excel is in "Edit Mode", macro will not run. So "enters edit mode, calculates the result, displays the value and moves cursor to the end of value" is trying conflicting this design.
     
    If you just want to display the value and formula, we can use for example:
    MsgBox Range("A1").Value
    MsgBox Range("A1").Formula
     
    However, we cannot use VBA to place Excel into "Edit Mode". I am not sure why you want to place Excel into "Edit Mode".
     
     
    I'll also run the most recent comment by the engineer to get his input.
    -brenda (ISV Buddy Team)
    Wednesday, December 28, 2005 4:25 PM
  • Hi,
    The reason I'm doing this, is to enable user to do in place editing of content, not the formula. Displaying message box or form dialog is not "the Excel way" of doing things, or let's put it this way: it would be much easier for end user to do it directly in Excel cell instead of in a some dialog, especially when there are a lot of cells. Imagine this situation: In cell A1 I have my custom Excel function =SHOWDBSTRING("northwind";"employee";1;1) defined for example in XLL... let's imagine SHOWDBSTRING queries some Database and in our case returns some string from position 1,1 (first column of the first row in table employee). Let's say it returns string value "foo". Currently, when you select A1 and press {F2} you get to edit formula. If I was able to alter behavior of {F2} to do my sequence of keys, I would be able to edit the value returned from the function, not the formula. I then make changes and my XLL intercepts this change, does a write directly into database and set the old formula back. After Excel does a refresh(recalculation) I would see the new, updated value in cell A1, returned from my custom function. I guess this sounds too complicated. As a workaround for this, I will probably implement OnDoubleClick/SheetBeforeDoubleClick event handler and show my custom form in there. Anyway thanks for the effort.

    Regards,
    Vlado
    Thursday, December 29, 2005 10:46 AM
  • My AddIn is written in cpp and code is running even in "edit mode" so i have to know if the user is abled to do actions or not !

    Any idea to resolve my pb ?

     

     

     

    Wednesday, January 04, 2006 11:47 AM
  • per our support engineer...

    Though Excel can detect "Edit mode", it does not expose to VBA. Therefore we cannot do it using the interface it exposes.
     
    It is natural that ActiveWorkbook->SaveCopyAs(newname) encounters errors, as we also cannot save in Excel when it is in "edit mode". As to this problem, I found several posts regarding it. You may try them. But it is better if Customer can press Enter to exit "edit mode" before saving Excel workbook. Then they have the same experience with Excel itself.
     

    -brenda (ISV Buddy Team)

    Wednesday, January 04, 2006 5:06 PM
  • Hi Vlado,

    Here's the engineer's response...

    Thanks for letting me know your detailed concern. Your idea is quite cool. I understand that you want to make Excel not only a viewer, but also a full-blown database application. However, by design, Excel is not a full-blown database application. I seldom see partners use Excel as an updater of database. It is more frequently used as a viewer. Your workaround is worth trying.
     
    -brenda (ISV Buddy Team)
    Wednesday, January 04, 2006 5:09 PM
  • Per the engineer:
     
    Depending on the version of Excel, you can check the "Application.Ready" property to see if Excel will accept automation commands. This property is available in VBA. However, I have checked internal group and have not found other ways to detect Excel "Edit mode". If you're using a version that doesn't have the "Ready" property, there's not a lot you can do.  C and C++ are no different from VBA in this case.
     
    I checked it is in Excel XP and Excel 2003.
     
    -brenda (ISV Buddy Team)
    Friday, January 06, 2006 7:25 PM
  •  

    Yes but Application.Ready is not an event !!! and tranfsorming a state reading in an event by a timer is ugly and buggen!!!!

    Saturday, January 07, 2006 11:06 AM
  • Hi Denis,

    According to the support engineer's research, there is no event exposed from Excel to indicate the change of edit mode. Our product group also does not mention any other means besides "Application.Ready" property.

    I'm really sorry that we haven't found you the answer you were looking for.

    -brenda (ISV Buddy Team)

    Monday, January 16, 2006 9:38 PM
  • I am writing a VSTO application, and I need to know progrannatically if the user is in edit mode. I noticed the thread on this subject and tried using the Application.ready property. This did not work as it always returned true - edit mode or not. My definition of edit-mode is when the cursor is in the formular bar and most of the command tools in excel are greyed out - the problem is my menu is still active and the user can still run my code.

    Is this possible in C#?

    Many thanks

    Funmi

    Monday, March 05, 2007 5:24 PM
  • Funmi-

    I am in a similar situation.  After a lot of searching, all the evidence I find indicates that you cannot programatically force the user out of Edit Mode (even using C# + Excel OM).  But if you find a way, please post it.  For your specific question of detecting whether the user is in edit mode, I did find a few references to testing the enabled state of the File > Open menu item.  For example: this post.

     

    In my VSTO 2005, Office 2003, C#, Excel doc-level solution, I am getting a crash when the user invokes a command in my code via a toolbar that I have added, when they are in edit mode. The crash occurs when I am setting the Style attribute on a Range, but I suspect there are many other Excel OM actions that would also be problematic.  My code is something like:

    Excel.Style style;

    // ... define the style ...

    Excel.Range cell = // some valid range;

    cell.Style = style; // this is what crashes when the end user has pressed F2 to enter edit mode on some (other) cell

     

    However: I noticed that certain code paths do cancel the edit mode. Now, before you get your hopes up too high, it is quite an ugly 'solution' and probably not usable as is.  It may however, open up some possible lines of investigation for other actions that have the effect of canceling edit mode.  In my code, I sometimes create (hidden) worksheets - this has the effect of canceling edit mode.  Something like:

     

    object lastSheet = (workbook.Worksheets.Count > 0 ? workbook.Worksheets.get_Item (workbook.Worksheets.Count - 1) : Type.Missing);

    sheet = workbook.Worksheets.Add (Type.Missing, lastSheet, Type.Missing, Excel.XlSheetType.xlWorksheet) as Excel.Worksheet;

     

    When I execute that snippet, I see that the current Edit Mode is cancelled.  Now, this is obviously quite heavy handed and too impractical to execute every time my toolbar menu item is invoked, but there may be other workbook- or worksheet-level actions that are less obtrusive and have the same effect: canceling edit mode.  Good luck, if I learn anything more useful I will post it, please do the same.

    -scoober

    Thursday, April 12, 2007 9:02 PM
  • I'm using C# and talking to Excel 2003 via the PIAs. Application.Ready doesn't seem to indicate whether Excel is in "Edit Mode" or not.

     

    Basically "Edit Mode" can mean a few things - but for me mostly it's when you either typing a formula in a cell, or in the formula bar and no calls are allowed through the PIA and there is no way to tell - you just end up dying with an exception.

     

    Is there really nothing that can be done!!?!!?!? Not even through a low level windows call?

    Friday, May 04, 2007 7:19 PM
  • Ok so here's my current kludge workaround for this issue.

    First get the worksheet menu and then look for the New.. submenu

    If it's disabled then we know that we're in Edit mode.

    this will probably work in 99% of the cases.

    Hope this works for others.

    Walt

     

    CommandBarControl oNewMenu = Application.CommandBars["Worksheet Menu Bar"].FindControl( 1, 18, Type.Missing, Type.Missing, true );

    if ( oNewMenu != null )

    {

    if ( !oNewMenu.Enabled )

    {

    throw new ApplicationException( "Excel is in Edit Mode" );

    }

    }

    Friday, May 04, 2007 8:33 PM
  • Hello,

     

    I think the link:

    http://www.codeproject.com/KB/office/Excel_Edit_Mode.aspx

    and

    http://devdoteloper.spaces.live.com/blog/cns!F37B99964521945!116.entry

     

    may help.

     

    They provide a workaround for the issue, though not 100% safe.

     

    Regards,

    Jialiang Ge

    Thursday, December 27, 2007 3:16 PM
  • WorkaroundI suggest you shift your editing solution to double clicking the cell.

    This way you may write something like:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' SAVE current fórmula from ActiveCell
    MyHiddenControlWKSHT.Range("flag_FormulaEditedToValue_Formula").Value =  ActiveCell.Formula
    SendKeys "{F2}{F9}{end}"
    Cancel = True
    MyHiddenControlWKSHT.Range("flag_FormulaEditedToValue_address").Value =  ActiveCell.Address
    MyHiddenControlWKSHT.Range("flag_FormulaEditedToValue_originalValue").Value =  ActiveCell.Value
    End Sub

    Having a _Change event for the Worksheet, code can test for these at the end of editing to take appropiate actions using the "cell hosted" parameters saved.

    Javier


    Sunday, July 05, 2009 2:15 AM
  • I'm not understanding any of this.

    In Excel 2003, I used to be able to use the keyboard to manuever to the cell that I wanted, then press F2 to edit the cell contents.

    Is there a comparable short cut for Excel 2007?   I have to keep moving to my mouse to double click on the cell in orrder to edit the old data.
    Monday, January 11, 2010 5:09 AM
  • Continuing Shaun Logan's post - there is a better way than Worksheets.add.
    If you change/activate a different worksheet, excel is forced out of edit mode.
    Thus you can exit edit mode by the following ugly code...
     
    		public void ExitEditMode(Workbook workbook)
    		{
    			//if there is no active sheet then there is no editing going on
    			if (workbook.ActiveSheet != null)
    			{
    				//can only switch worksheets if there are more than one of them 
    				if (workbook.Worksheets.Count > 1)
    				{
    					Worksheet activeWorksheet = workbook.ActiveSheet;
    					if (activeWorksheet == workbook.Worksheets[1])
    					{
    						workbook.Worksheets[2].Activate();
    					}
    					else
    					{
    						workbook.Worksheets[1].Activate();
    					}
    					activeWorksheet.Activate();
    				}
    			}
    		}
    
     
    ps. It will make the screen flicker as the worksheets are activated and I have not tested the speed of it if your worksheets contains huge amounts of data.
    Wednesday, October 05, 2011 9:20 AM