How to get user to select cells(Range input) in c# vsto excel add-in?

Question

I need is to give the user control to select some cells which'll be used in the program. The interface is easily seen in many of the excel functions like while inserting data to the graphs.

Pre-selected range can be easily accessed as Globals.ThisAddIn.Application.ActiveWindow.RangeSelection but this is not what I am looking into as I need multiple such ranges. I am trying this on visual-studio, There is Interaction.InputBox function to take string in , which is said to be equivalent of Inputbox of vba, but inputbox of vba had parameter for input data type, which c# inputbox doesn't.

The VBA equivalent for this is

Dim rngX as Range
Set rngX = Application.InputBox("Select a cell or a range", "Input Cells", Type:=8)

the Type:=8 will make the inputbox to take range input. but i need to do it in c# (using visual studio) and the Inputbox method of c# doesn't have input type like VBA.

score 5 · Accepted Answer · answered Mar 13 '20 at 04:03

5

Range myRange;

myRange = Globals.ThisAddIn.Application.InputBox("Please select your range?", "Range Selector", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);

answered Mar 13 '20 at 04:03

tariqj

136
1
7

2

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Igor F. Mar 13 '20 at 07:31
thanks it works like a charm, but as the previous comment says you need to explain a bit more so others can benefit from it. – Atreyagaurav Mar 14 '20 at 06:41
Thank you for taking the time to answer. Unlike the other commentators, I don't think this needs any more explanation - it's pretty obvious what this is doing! Sometimes a short, to-the-point answer is exactly what is needed. – mohamedmoussa May 25 '20 at 05:52

score 0 · Answer 2 · answered Nov 11 '19 at 15:16

I tried a lot but couldn't find the exactly what I was looking for, but this workaround works for now. I made the form modeless and then put whatever operation I needed to do with the range inside that form, so I couldn't actually send the range back from the form, but I could at least perform the task.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic;

namespace ExcelAddInZeroSofts
{
    public partial class InterpolationForm : Form
    {
        Worksheet ws;
        public InterpolationForm(Range rng)
        {
            InitializeComponent();
            tbRange.Text = rng.Address;
        }
        private void SelectRangeForm_Load(object sender, EventArgs e)
        {
            ws = Globals.ThisAddIn.Application.ActiveSheet;
            ws.SelectionChange += ws_SelectionChange;
            // This one will add the event handling to worksheet.
        }
        void ws_SelectionChange(Range Target)
        {
            if (this.ActiveControl.Name.First() == 't')
            {
                this.ActiveControl.Text = Target.Address;
                // This one will add the address to the userform (I have 3 fields).
            }
        }

        private void SelectRangeForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            ws.SelectionChange -= ws_SelectionChange;
        }

        private void bSubmit_Click(object sender, EventArgs e)
        {
            // My main Logic Goes here
            this.Close();
        }

        private void bCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

I still hope someone will give the way to have it work like the original VBA inputbox.

How to get user to select cells(Range input) in c# vsto excel add-in?

2 Answers2