VSTO and Excel--End Cell Edit Mode?

[Jim Tilson]

Is there a way to programmatically end edit mode in a cell if a user has
edited a cell's contents, but has not exited edit mode before triggering code
in my OfficeCodeBehind class? Since the cell is still in edit mode, my
code-behind is not able to see the new value in the cell.

Thanks

 

[Jim Rech]

Since the cell is still in edit mode, my code-behind is not able to see
But this no new value until the user presses Enter. I know of no way to
find what is in the Formula Bar in Edit mode. I don't use VSTO but in
normal VBA no events are triggered and no code can run in Edit mode. Is it
different via VSTO?


--
Jim
| Is there a way to programmatically end edit mode in a cell if a user has
| edited a cell's contents, but has not exited edit mode before triggering
code
| in my OfficeCodeBehind class? Since the cell is still in edit mode, my
| code-behind is not able to see the new value in the cell.
|
| Thanks
|
| --
| Jim Tilson
| MCP

 

[Jim Tilson]

Right--What I need to be able to do is to commit any outstanding changes in
the cell so that the value is available to my code behind.

For example, I have a menu I added to the Excel toolbar to perform a handful
of functions. One of those functions is to save the spreadsheet data to a
database. If the user begins to edit a cell, then *while the cell is in edit
mode* clicks the menu and chooses the "Save to Database" command, I need a
way to end edit mode to make that new value available to my code so that the
new value can be saved to the database.

 

[Peter Huang [MSFT]]

Hi

In Edit Mode, the Excel messge will enter a loop(similar as a modal
dialog,msgbox), so most of menu and button will be grey out.
Based on my test, it will work in the Save Button.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
MsgBox Application.ActiveCell.AddressLocal
Debug.Print "Save"
Cells(1, 1).Select
End Sub

I think you may try to do the similar job in your own button click.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jim Tilson]

It did not work. The cell is still in edit mode.

Your event handler code below is for the Workbook BeforeSave event. I'm
talking about my own event handler to save the spreadsheet data to the
database--I'm not running through the WorkBook's file save event.

 

[Peter Huang [MSFT]]

Hi

Sorry for confusion, as I said before, when the cell in edit mode, the
message loop is similar with a modal dialog, most of the message will not
work.

So far do further research to see if there is any other way to do the job
with a button_click of our customized button.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi

Based on my research, I think you may try to use the SendKeys approach to
send the Enter keyboard key to the active cell, just as we do manually.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
cb = ThisApplication.CommandBars.Add("Test", , False, True)
cbb = cb.Controls.Add(Office.MsoControlType.msoControlButton, , , ,
True)
cbb.Caption = "Hello:"
cbb.Tag = "1:"
cbb.FaceId = 17
End Sub

Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles
ThisWorkbook.BeforeClose
Cancel = False
cbb.Delete(True)
cb.Delete()
End Sub

Private Sub cbb_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
Handles cbb.Click
SendKeys.Send("{ENTER}")
End Sub

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Dave Peterson]

That sounds kind of dangerous to me.

I sometimes type something in the cell, then realize that it's wrong before I
hit enter. So I hit escape to leave the cell as-is (as-was?).

If I'm typing a formula, the enter key could just yell at me that my formula
isn't correct.

And if I'm evaluating a part of the formula (select and hit F9), then that
Sendkeys may have just screwed up my real formula.

I can't offer anything better. But sometimes warnings are good <bg>.

 

[Peter Huang [MSFT]]

Hi

Thanks for your response.
Also if you do not want to confirm the input in the cells, I think you may
try to send the "ESC" key to cancel your edit.
SendKeys.Send("{ENTER}")

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Dave Peterson]

But that's the other side of my warning.

I don't think a developer can actually know whether to confirm or escape from
what the user is doing. Assuming either case applies could lead to trouble.

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly reply!
From your description, you wants a button, when clicked it will confirm the
change when a cell in edit mode.
So in your scenario, if you want to provide the customer with two choices(I
think only the customer know he wants to cancel/confirm the change in the
cell), that is to make two buttons, one is to Cancel, the other to confirm.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Dave Peterson]

I just butted into the conversation with some warnings.

But I'd bet that the OP wants to have full control over what happens. And I
don't think that's always a good thing--and I don't think it's always possible.

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly reply!
As I said before, the Cell Edit Mode is similar the Modal Dialog, it will
have its own message loop.
e.g.
If you popup a msgbox in your Excel Macro, you will find that you can not
click any other place than the modal dialog, until it is closed.

The Edit Mode cell is of the similar scenario, Excel automation is all
based on the Excel Object Modal, but the OM did not expose such approach
for us to confirm/cancel the Edit Mode.
so far our workaround is to simulate the End User's keyboard behavior to
Confirm/Cancel the Edit Mode.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Dave Peterson]

Not to beat a dead horse, but that's the problem.

No matter which one you choose, you could be messing the user up. I just don't
understand how a developer could make the decision to cancel or confirm with no
clue to what the user wants.

(I still don't have a better solution, though <vbg>.)

 

[Ollie]

Ultimately you would want to disable your buttons/menus when in Edit
Mode.

I too cannot believe that the only way round this is to use SendKeys.

How do I submit an enhancement request to Microsoft???

 

[Dave Peterson]

(e-mail address removed)

Ultimately you would want to disable your buttons/menus when in Edit
Mode.

I too cannot believe that the only way round this is to use SendKeys.

How do I submit an enhancement request to Microsoft???