denisJaubert


How can i detect "edit mode" in excel macros

Thank's for idea(s)




Re: Excel edit mode

MS ISV Buddy Team


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)






Re: Excel edit mode

denisJaubert

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





Re: Excel edit mode

Vladislav Malicevic

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!




Re: Excel edit mode

MS ISV Buddy Team

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)






Re: Excel edit mode

MS ISV Buddy Team

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)






Re: Excel edit mode

Vladislav Malicevic

Hi,
my scenario is a bit more complicated. Lets 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} C 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





Re: Excel edit mode

any name

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.




Re: Excel edit mode

MS ISV Buddy Team

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)





Re: Excel edit mode

Vladislav Malicevic

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




Re: Excel edit mode

denisJaubert

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

 

 

 





Re: Excel edit mode

MS ISV Buddy Team

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)






Re: Excel edit mode

MS ISV Buddy Team

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)





Re: Excel edit mode

MS ISV Buddy Team

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)





Re: Excel edit mode

denisJaubert

 

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