locked
VBA -> C# Help RRS feed

  • Question

  • Sub ine()
        For Each sh In ActiveWorkbook.Worksheets
            With sh
                 .DrawingObjects(1).OnAction = "test"
            End With
        Next
    End Sub

    I have a Button (Form Control) on Excel and i need to edit his action.

    How can i convert this to C# ? 

    I have tried:

    ((Excel.Worksheet)with.ActiveWorkbook.Worksheets).DrawingObjects(1).OnAction = ""; 

    but it's showing that "does not contain a definition for "OnAction" and no extension method..."

    Monday, January 18, 2016 7:39 AM

Answers

  • >>>I have a Button (Form Control) on Excel and i need to edit his action.

    According to your description, you could refer to below code:

    Excel.Worksheet sh = Globals.ThisWorkbook.Worksheets.get_Item(1);
    Excel.Shape btn = sh.Shapes.Item(1);
    
    String updateBT "...// macro code for button";  
    VBA.VBComponent oModule1 = Globals.ThisWorkbook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
    oModule1.Name = "Update";
    oModule1.CodeModule.AddFromString(updateBT);
    
    btn.Name = "Update";
    btn.OnAction = "... // name of your macro code";
    btn.OLEFormat.Object.Caption = "... // Button name";
    

    For more information, click here to refer about How to: Add Windows Forms Controls to Office Documents

    • Marked as answer by David_JunFeng Sunday, January 31, 2016 2:50 PM
    Wednesday, January 20, 2016 9:34 AM

All replies

  • Sub ine()
        For Each sh In ActiveWorkbook.Worksheets
            With sh
                 .DrawingObjects(1).OnAction = "test"
            End With
        Next
    End Sub

    I have a Button (Form Control) on Excel and i need to edit his action.

    How can i convert this to C# ? 

    I have tried:

    ((Excel.Worksheet)with.ActiveWorkbook.Worksheets).DrawingObjects(1).OnAction = ""; 

    but it's showing that "does not contain a definition for "OnAction" and no extension method..."

    • Merged by David_JunFeng Wednesday, January 20, 2016 7:48 AM duplicated
    Friday, January 15, 2016 12:18 PM
  • Hello Julius,

    VSTO doesn't provide anything for that. I'd suggest asking Excel specific questions on the Excel for Developers forum instead. 

    Here is what you may see for the Worksheet.DrawingObjects method in MSDN:

    This API supports the Visual Studio infrastructure and is not intended to be used directly from your code.

    Friday, January 15, 2016 8:59 PM
  • public void ine()
    {
    	foreach (var sh in ActiveWorkbook.Worksheets)
    	{
    		 sh.DrawingObjects(1).OnAction = "test";
    	}
    }

    Hope it helps ;)
    Also, for such conversions you can use an app called Instant C#, VB conversions.

    Thanks in advance, Ciprian LUPU

    • Proposed as answer by André Santo Monday, January 18, 2016 12:05 PM
    Monday, January 18, 2016 11:09 AM
  • Monday, January 18, 2016 11:10 AM
  • >>>I have a Button (Form Control) on Excel and i need to edit his action.

    According to your description, you could refer to below code:

    Excel.Worksheet sh = Globals.ThisWorkbook.Worksheets.get_Item(1);
    Excel.Shape btn = sh.Shapes.Item(1);
    
    String updateBT "...// macro code for button";  
    VBA.VBComponent oModule1 = Globals.ThisWorkbook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
    oModule1.Name = "Update";
    oModule1.CodeModule.AddFromString(updateBT);
    
    btn.Name = "Update";
    btn.OnAction = "... // name of your macro code";
    btn.OLEFormat.Object.Caption = "... // Button name";
    

    For more information, click here to refer about How to: Add Windows Forms Controls to Office Documents

    • Marked as answer by David_JunFeng Sunday, January 31, 2016 2:50 PM
    Wednesday, January 20, 2016 9:34 AM