VSTO Display Selected Cell Reference in Text Box

Question

I have a Windows Form with a list of values to display each with a corresponding text box that tells the program what cell to display it in. Currently the user inputs a range (ex. "A1", "G6", etc...) for each value they want shown and leave the remaining blank.

Is there any way to click into the text box, and then click in a cell and have that cell reference appear in the text box (like how Goal Seek works)?

Answer 1

In your VSTO code, you should be able to handle the Worksheet SelectionChange event

For example, in a VSTO project we own, we subscribe to the OnChange event of each worksheet at startup:

  private List<Excel.Worksheet> _Worksheets = new List<Excel.Worksheet>();

  private void ThisWorkbook_Startup(object sender, System.EventArgs e)
  {
   foreach (Excel.Worksheet sheet in Worksheets) //Could test for sheet name here
   {
    _Worksheets.Add(sheet);
    sheet.SelectionChange += 
        new Excel.DocEvents_SelectionChangeEventHandler(Sheet_SelectionChange);
   }
  }

You should be able to then get the ActiveCell in your handler, something like:

Excel.Range activeCell = (Excel.Range) this.Application.ActiveCell;

//get the cell value (or other properties)
object value = rng.Value;

Once you have a reference to the cell, you can get the address by using the code from this answer.

You should then be able to update the value of your textbox.