Excel edit mode

Excel edit mode

• lumpynose

  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.
  
  

• MilwaukeeCharlie

  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

   

   

   

  
  

• sa acc

   

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

  
  

• Kyaw N Paing

  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)

  
  
  

• nkoranda

  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:

  http://msdn.microsoft.com/library/en-us/vbaxl11/html/Welcome_HV01134775.asp

   

  Could you let me know the detailed scenario in which you want to implement this functionality
  ***
  
  thanks,
  -brenda (ISV Buddy Team)

  
  
  

• Henry Chan

  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)

  
  
  

• Stathread01

  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
  
  

• smgraham

  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

  
  

• Matthew McDonald

  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.

   

  Excel Interop issues with a new spreadsheet
  http://www.eggheadcafe.com/forumarchives/NETFrameworkinterop/Aug2005/post23267291.asp
  http://www.hightechtalks.com/showthread.php p=6904749#post6904749
  http://www.codeproject.com/vb/net/ActivateSingleAppInstance.asp

  -brenda (ISV Buddy Team)

  
  
  

• Zamial

  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)

  
  
  

• jpguest

  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.

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbaxl11/html/xlproReady_HV03081037.asp 
  http://msdn.microsoft.com/library/en-us/vbaxl10/html/xlproReady.asp 

   

  -brenda (ISV Buddy Team)

  
  
  

• Sealed

  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)

  
  
  

• GregStroud

  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!
  
  

• antho

  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
  
• Excel edit mode