buttons on the spreadsheet to interact with my application

[robertb_NZ]

I have a VB.NET application that creates and displays an Excel spreadsheet, ending up with

' Make sure Excel is visible and give the user control of Excel's lifetime.
xlApp.Visible = True
xlApp.UserControl = True​

The user can then edit the spreadsheet and save it, returning to the application at the next statement. I'd like to put some buttons on the spreadsheet to interact with my application, but my Internet search has only found articles showing me how to use Excel buttons to run macros and VBA code. Can somebody please point me at an appropriate tutorial, or tell me what do to?

Thank you, Robert

 

[jmcilhinney]

I don't know the answer to your question and am unlikely to as I don't use Office much at all but I would suggest that you provide a bit more detail about the kind of interaction you're trying to achieve as it may be helpful in determining the best solution.

 

[robertb_NZ]

Ok John, here goes. I hope it's not so much detail that readers get overwhelmed.

Background: the overall problem
Jazz is a programming system, described at Jazz Software, that generates COBOL programs for IBM mainframes. A customer is particularly interested in problems of file conversion, in which files are copied with changes to create a new version of a mainframe application, for example making a version for a different customer. To plan these conversions the customer develops Excel spreadsheets: imagine a spreadsheet with three groups of columns: -

A Columns describing the input file
B Columns describing the output file
C A mapping section that describes the transformation:

SAME: input field => Output field
FILL: Output field is initialized (usually blank or zero)
etc​

We are trying to automate their spreadsheet-based process. The process is: -

Process 1: A conversion spreadsheet is produced by Jazz based on the input and output record descriptions. A default Mapping value is given
Process 2: This spreadsheet may be edited: changing field names, formats, mapping values, etc. Lines may be added to define new fields, possibly redefining existing fields. This is Excel only, and can be independent of Jazz.
Process 3: The [edited] spreadsheet is read back and validated: if valid, the conversion program is generated.​

Process 3 works perfectly provided that the spreadsheet either has not been edited, or at least has no errors and still agrees perfectly with the record definitions. The current issue triggering this post: my program needs to deal with any reasonable edit of the spreadsheet, detecting errors including differences between the record descriptions and the spreadsheet and offering easy ways of correcting these errors.

My Current Logic
Process 3 reads the spreadsheet and checks that it was originally produced by Process 1, and that the input and output record definitions exist. If OK, then

Dim xlApp As Excel.Application = New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet​

will have been set, and the program reaches this logic where the spreadsheet is displayed and the data lines are validated and stored as structure XLLine in collection Excellines: -

ExcelLines.Clear()
xlApp.Visible = True
Dim NextInputData2 As Integer = 1
Dim InputOutOfSequence As Boolean = False
Dim NextOutputData2 As Integer = 1
Dim OutputOutOFSequence As Integer = False
For Row As Integer = DataRow To lastUsedRow

HighlightThisRow(Row, lastUsedColumn)
Dim XLLine As New ExcelLine
GetLineFileData(Row, InputData1, XLLine, NbrInput, NextInputData2, InputOutOfSequence)
GetLineFileData(Row, OutputData1, XLLine, NbrOutput, NextOutputData2, OutputOutOFSequence, NbrInput, NbrInput + NbrOutput + NbrGenrl)
GetLineGenrlData(Row, XLLine, NbrGenrl, NbrInput + NbrOutput)
ExcelLines.Add(XLLine)​

Next
ResetLastRow(DataRow, lastUsedColumn)​

At this point, the spreadsheet has been displayed, but the VB application is still in control. As the For loop works down the data area the current line is highlighted, and its information is put into XLLine. If there are any problems discovered by GetLineFileData or GetLineGenrlData then XLLine.Message will have been set.

My plan is to enhance the logic of this loop so that, if an error is detected, the VB dialog offers appropriate options to fix it. However there is a problem: although we haven't executed

xlApp.UserControl = True​

there is nothing to stop the user editing or closing the spreadsheet. Such actions can cause the VB program to throw an error.

Options

1. Display the spreadsheet as Read Only. I'd still need to prohibit closing it. How do I do this?
2. Keep the spreadsheet hidden until all editing is finished. While this would solve the problem, it wouldn't provide a good user experience.
3. Control this process from the spreadsheet. This requires a solution to my original query, how to make a spreadsheet button call back to the VB.Net program.
4. Any other ideas?

Thank you, Robert.

 

[JohnH]

Excel: How to run C# code behind with a click of a button on a Worksheet, without VBA code

blogs.msdn.microsoft.com

It says "COM add-in", but it will for other project types too. The example shows adding a button to sheet and attaching an event handler in C#.
You can add buttons (ActiveX commandbuttons) manually to sheet, and attach to them in VB later. Example:

Private WithEvents CmdBtn As Microsoft.Vbe.Interop.Forms.CommandButton 'Requires COM reference MS Forms 2.0
Private Sub CmdBtn_Click() Handles CmdBtn.Click
    Debug.WriteLine("excel command button clicked")
End Sub

CmdBtn = CompilerServices.NewLateBinding.LateGet(sheet, Nothing, "CmdButton1", Nothing, Nothing, Nothing, Nothing)