VSTO BeforeDoubleClick edit mode

[Ed White]

I want to be able to double-click on a cell in an Excel spreadsheet and have
it run my VSTO code, and just ignore the cell I double-clicked on. However,
when you double-click on a cell in Excel, it puts the cell into Edit mode,
which messes up my code. How can I tell it to get out of Edit mode? (I'm
using VSTO with VS 2008 and Excel 2007).

 

[desertres]

I could be easily mistaken, but I think you would have to insert some code
into the worksheet object so that you override that doubleclick...there is a
Before_DoubleClick subroutine on each worksheet.

 

[Ji Zhou [MSFT]]

Hello Ed White,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

First, I want to ensure I understand the issue correctly. From the
description, it is a VSTO Excel project. The objective is calling VSTO
codes, but does not make the Excel enter into edit mode, when end user
double clicks a cell in the Excel sheet. If I misunderstood your issue,
please feel free to let me know.

If I have understood, then I think we can subscribe this.Application.
SheetBeforeDoubleClick event and, put our codes in the event handle
function. The event handle function has a parameter declared as "ref bool
Cancel". After we set the "Cancel" parameter to true, the default behavior
of Excel will be canceled. That is to say, double clicking will not make
the cell enter into edit mode.

I tested the following codes on my side, and it worked. If you have any
future questions or concerns, please feel free to let me know.
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
this.Application.SheetBeforeDoubleClick += new
Microsoft.Office.Interop.Excel.AppEvents_SheetBeforeDoubleClickEventHandler(
Application_SheetBeforeDoubleClick);
}

void Application_SheetBeforeDoubleClick(object Sh,
Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
{
Cancel = true;
MessageBox.Show("Double click");
}


Best regards,
Ji Zhou ([email protected], remove ‘online.’)
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

This helps, but I still have a problem. First, I'm using VB. The problem
occurs when I try to select a cell or range of cells on a different
spreadhseet than the one the BeforeDoubleClick was called on. For example:

Private Sub Sheet5_BeforeDoubleClick(ByVal Target As
Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) Handles
Me.BeforeDoubleClick
Stop
Cancel = True
Target.Select()
Globals.Sheet8.Range("Ticker").Value = Target.Value
'Globals.Sheet8.Range("Ticker").Select()
MsgBox("Got here to Sheet5")
Globals.Sheet8.EvaluateTkr()
End Sub

Friend Sub EvaluateTkr()
Me.Range("Ticker").Select()
MsgBox("Got here to Sheet8")
End Sub

When you double-click a cell on Sheet5, and you step through the code, the
first three lines after Stop are executed (including any Range(...).Select
statements on Sheet5), but the code seems to stop running when
Globals.Sheet8.Range("Ticker").Select() is executed. Further, if you comment
out or delete that line where it stalls, it will get to EvaluateTkr on
Sheet8, but will then stall on the first line (i.e. you never see "Got here
to Sheet8"). After the code stalls, if you click once on the Excel
spreadsheet to activate it, it looks like it is in the middle of edit mode on
the cell you clicked. So I don't know if I properly translated your code
from C# to VB or if something else is amiss, but I just can't
Range(...).Select a cell on any spreadsheet other than the one I
double-clicked on without stopping the program execution, and the fact that
Excel appears to remain in Edit mode seems to be the problem (but not sure).

--
Ed

Hello Ed White,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

First, I want to ensure I understand the issue correctly. From the
description, it is a VSTO Excel project. The objective is calling VSTO
codes, but does not make the Excel enter into edit mode, when end user
double clicks a cell in the Excel sheet. If I misunderstood your issue,
please feel free to let me know.

If I have understood, then I think we can subscribe this.Application.
SheetBeforeDoubleClick event and, put our codes in the event handle
function. The event handle function has a parameter declared as "ref bool
Cancel". After we set the "Cancel" parameter to true, the default behavior
of Excel will be canceled. That is to say, double clicking will not make
the cell enter into edit mode.

I tested the following codes on my side, and it worked. If you have any
future questions or concerns, please feel free to let me know.
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
this.Application.SheetBeforeDoubleClick += new
Microsoft.Office.Interop.Excel.AppEvents_SheetBeforeDoubleClickEventHandler(
Application_SheetBeforeDoubleClick);
}

void Application_SheetBeforeDoubleClick(object Sh,
Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
{
Cancel = true;
MessageBox.Show("Double click");
}


Best regards,
Ji Zhou ([email protected], remove ‘online.’)
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ji Zhou [MSFT]]

Hello Ed White,

Thanks for your detailed information! I can reproduce the problem with the
codes you provide. From test on my side, I find that the problem is caused
by an unhandled exception, rather than the edit mode we talked above.

To select a cell in another sheet by codes, firstly we need to call the
Sheet.Activate() method to activate the target sheet, and then call the
Range.Select() method to select that cell. Otherwise an exception will be
thrown with error message "The Select method of Range class failed". Codes
below that line will not get executed.

The following codes have already worked on my side:

Private Sub Sheet5_BeforeDoubleClick(ByVal Target As
Microsoft.Office.Interop.Excel.Range, ByRef Cancel As System.Boolean)
Handles Me.BeforeDoubleClick
Stop
Try
Globals.Sheet8.Range("Ticker").Value = Target.Value
Globals.Sheet8.Activate()
Globals.Sheet8.Range("Ticker").Select()
MsgBox("Got here to Sheet5")
Globals.Sheet8.EvaluateTkr()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Friend Sub EvaluateTkr()
Try
Me.Activate()
Me.Range("Ticker").Select()
MsgBox("Got here to Sheet8")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

If we comment the two lines of Globals.Sheet8.Activate() and Me.Activate(),
and then debug it in Visual Studio, we can find the code flow will enter
into the Catch block. In VSTO projects, the unhandled exceptions may be
swallowed silently by Office application. So, I suggest you always put the
codes in a Try/Catch block like above codes did. Please let me know if you
have any future questions on this.


Best regards,
Ji Zhou ([email protected], remove ‘online.’)
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

That worked...thanks.
Also, knowing that VSTO does not always show expections is very helpful.
Regards,
Ed

 

[chan]

how Ji Zhou, i like this post , it works.

are you still on v-not position , i wish you can still able to answer my
question, i have a similar issue but it is with a quite complex on close ,
hide, unload behavior with user clicking anywhere in template after macro and
dataload.
Scenario with the problem:
in Excel Template , Open enable the macro, user selecting the data from
a combo box drop downlist , querying data and getting the result back
that populate on to sheet with a template being load.

Base one this ‘ sheet’ say as ‘ Base product1’. is being loaded,

1--at this stage if we allow the splash screen to unload on itself. And the
user does not clicking on any where in the worksheet, the file closes abruptly
in here i have code:
public void Start()
{
// Run the form
this.Opacity= .99;
Application.Run(this);
this.Dispose();
}

public void CloseNice(object o, EventArgs e)
{
// Loop to fade the splash in 1 sec down to 0 opacity
for( int i= 19; i> 0; i-- )
{
this.Opacity= ((float)i)/ 20;
Thread.Sleep( 50 );
}
this.Close();
}
--- /// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}


we are subspecting ; in 1- closenice scope . if we alow the user to unload
iiseft.
also the excel template is closes abruptly iif not any clicking (key down )
in the sheet.

2--Instead if the user set the focus to some where in the work sheet by
clicking on a cell, the file is not closed and can se the combo populated
with the Base Product1 .
if it is clcking with any set the focus -- keydown,,, that sheet is not
close and the combo box is not dispose.

in 2- i have not idea why?

/// <summary>
/// Called before the workbook is closed. Note that this method
/// might be called multiple times and the value assigned to Cancel
/// might be ignored if other code or the user intervenes.
/// </summary>
/// <param name="Cancel">False when the event occurs. If the event procedure
/// sets this to true, the document does not close when the procedure is
finished.
/// </param>
protected void Workbook_BeforeClose(ref bool Cancel)
{
// Send the unlock call
this.cService.UnLockProduct("", this.DMP_APP_NAME);
Cancel = false;
}
/// <summary>
/// Has called when selection is changed
/// </summary>
where do you think of that can cuase #2 issue?


thank you advance

Pinkychan

Hello Ed White,

Thanks for your detailed information! I can reproduce the problem with the
codes you provide. From test on my side, I find that the problem is caused
by an unhandled exception, rather than the edit mode we talked above.

To select a cell in another sheet by codes, firstly we need to call the
Sheet.Activate() method to activate the target sheet, and then call the
Range.Select() method to select that cell. Otherwise an exception will be
thrown with error message "The Select method of Range class failed". Codes
below that line will not get executed.

The following codes have already worked on my side:

Private Sub Sheet5_BeforeDoubleClick(ByVal Target As
Microsoft.Office.Interop.Excel.Range, ByRef Cancel As System.Boolean)
Handles Me.BeforeDoubleClick
Stop
Try
Globals.Sheet8.Range("Ticker").Value = Target.Value
Globals.Sheet8.Activate()
Globals.Sheet8.Range("Ticker").Select()
MsgBox("Got here to Sheet5")
Globals.Sheet8.EvaluateTkr()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Friend Sub EvaluateTkr()
Try
Me.Activate()
Me.Range("Ticker").Select()
MsgBox("Got here to Sheet8")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

If we comment the two lines of Globals.Sheet8.Activate() and Me.Activate(),
and then debug it in Visual Studio, we can find the code flow will enter
into the Catch block. In VSTO projects, the unhandled exceptions may be
swallowed silently by Office application. So, I suggest you always put the
codes in a Try/Catch block like above codes did. Please let me know if you
have any future questions on this.


Best regards,
Ji Zhou ([email protected], remove ‘online.’)
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.