Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Excel edit mode

How can i detect "edit mode" in excel macros ?

Thank's for idea(s)

[69 byte] By [denisJaubert] at [2007-12-18]

«« Alternating Items in VS Report Control
»» Tab Control / Tab Pages (BUG or Feature)

# 1

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)

MSISVBuddyTeam at 2007-9-8 >

top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...

# 2

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

denisJaubert at 2007-9-8 >

# 3

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!

VladislavMalicevic at 2007-9-8 >

# 4

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)

MSISVBuddyTeam at 2007-9-8 >

# 5

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)

MSISVBuddyTeam at 2007-9-8 >

# 6

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

VladislavMalicevic at 2007-9-8 >

# 7

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.

anyname at 2007-9-8 >

# 8

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)

MSISVBuddyTeam at 2007-9-8 >

# 9

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

VladislavMalicevic at 2007-9-8 >

# 10

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 ?

denisJaubert at 2007-9-8 >

# 11

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)

MSISVBuddyTeam at 2007-9-8 >

# 12

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)

MSISVBuddyTeam at 2007-9-8 >

# 13

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)

MSISVBuddyTeam at 2007-9-8 >

# 14

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

denisJaubert at 2007-9-8 >

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Question about 'Excel edit mode', No.205840,Posted by denisJaubert

Excel edit mode

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Site Classified