Ok, The problem appears to be caused by setting a cell other than the
calling cell ...
If you create a command bar button you can write to any cell that buttons
click event ...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to
remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelAddInFuncSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion
/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{
private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;
public Connect()
{
}
public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;
}
public void OnAddInsUpdate(ref System.Array custom)
{
}
public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;
try
{
oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }
// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }
try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}
m_CommandBar.Visible = true;
CreateButton();
oStandardBar = null;
oCommandBars = null;
}
public void OnBeginShutdown(ref System.Array custom)
{
}
private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;
for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}
if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );
m_Button.Enabled = true;
m_Button.Caption = "Set Cell J10";
m_Button.Style = MsoButtonStyle.msoButtonCaption;
m_Button.Tag = "MyButton";
m_Button.Visible = true;
m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );
// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );
try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}
}
#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion
}
}
Frank M. Walter said:
Sorry...
Exception has been thrown by the target of invocation.
In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });
It was this function...