none
access data on combobox from another sheet RRS feed

  • Question

  • Hello:

    I have sheet 1 and sheet 2. In Sheet 1 I have a cell that contains certain values, those values are the same as the values found on combobox1 on sheet 2. I want to have a button on sheet 1 that when clicked, it sends the value to combobox1 in sheet 2.

    I have not been able to figure out how to access comboboxes and list boxes from a different sheet.

    Sunday, August 25, 2013 12:07 AM

Answers

  • Hi,

    Base on my understanding, you want to get a comboBox control from different sheet.

    As you post in the VSTO forum, I suppose you use VSTO technology.

    I created a document-level add-in, add code like below:

     

      private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                Microsoft.Office.Tools.Excel.Controls.Button btn = Globals.Sheet1.Controls.AddButton(Globals.Sheet1.Range["A1"], "btn1");
                Microsoft.Office.Tools.Excel.Controls.ComboBox cbo = Globals.Sheet2.Controls.AddComboBox(Globals.Sheet2.Range["A2"], "cbo1");
                btn.Name = "btn1";
                cbo.Name = "cbo1";
                Globals.Sheet1.Range["A5"].Value = "a1\r\na2\r\na3";
              btn.Click += btn_Click;
            }
    
     void btn_Click(object sender, EventArgs e)
            {
                string text = Globals.Sheet1.Range["A5"].Value;
                string[] strItem = text.Split(new string[] { "\r\n" },StringSplitOptions.RemoveEmptyEntries);
                (Globals.Sheet2.Controls["cbo1"] as Microsoft.Office.Tools.Excel.Controls.ComboBox).Items.AddRange(strItem);
            }

    If I misunderstood, please let me know.


    Tuesday, August 27, 2013 12:31 AM

All replies

  • Hi,

    Base on my understanding, you want to get a comboBox control from different sheet.

    As you post in the VSTO forum, I suppose you use VSTO technology.

    I created a document-level add-in, add code like below:

     

      private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                Microsoft.Office.Tools.Excel.Controls.Button btn = Globals.Sheet1.Controls.AddButton(Globals.Sheet1.Range["A1"], "btn1");
                Microsoft.Office.Tools.Excel.Controls.ComboBox cbo = Globals.Sheet2.Controls.AddComboBox(Globals.Sheet2.Range["A2"], "cbo1");
                btn.Name = "btn1";
                cbo.Name = "cbo1";
                Globals.Sheet1.Range["A5"].Value = "a1\r\na2\r\na3";
              btn.Click += btn_Click;
            }
    
     void btn_Click(object sender, EventArgs e)
            {
                string text = Globals.Sheet1.Range["A5"].Value;
                string[] strItem = text.Split(new string[] { "\r\n" },StringSplitOptions.RemoveEmptyEntries);
                (Globals.Sheet2.Controls["cbo1"] as Microsoft.Office.Tools.Excel.Controls.ComboBox).Items.AddRange(strItem);
            }

    If I misunderstood, please let me know.


    Tuesday, August 27, 2013 12:31 AM
  • Thank you, this is what I needed
    Friday, September 13, 2013 2:48 AM