vb.net add a combobox to excel - Xtreme Visual Basic Talk

06-19-2012, 11:21 AM

dizo

Newcomer

Join Date: Jun 2012

Posts: 2

vb.net add a combobox to excel

Hello
I am creating a new software with visual studio 2010 using the program language visual basic .NET, well in that software I create a new file in excel 2007, my question is because I do not know how I can create a combobox with data of my database inside, I will be appreciate your help a lot, thank for your time.
To create the combobox I use the next code in a button of my windows form:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlcombobox As Excel.OLEObject
Dim iStartLine As Long

xlApp = New Excel.Application
xlApp.Visible = True
xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.ActiveSheet

xlcombobox = xlSheet.OLEObjects.Add(ClassType:="Forms.combobox.1", _
Link:=False, DisplayAsIcon:=False, Left:=1, Top:=25, Width:=72, Height:=24)
xlcombobox.Name = "clientes"

I don’t know how to continue help me please

06-19-2012, 05:27 PM

surfR2911

Contributor

Join Date: Oct 2009

Posts: 719

dizo's dilemma

Hi dizo and welcome to the forum.

I'm not an excel expert but maybe I can give you some more help with Excel 2007 OleObjects
than you got when you posted elsewhere, here and here.

A quick search found this offsite thread
that indicates a possible solution might be something like:

Code:

xlcombobox.Object.AddItem "SomeData"

..or using the code offered by someone name "XL-Dennis" in
this social.msdn forum thread the other way might be:

Code:

With xlcombobox.Object
       .Additem("VSTO")
       .Additem("Excel")
       .Additem("Dennis")
       .LinkedCell = "B1"
End With

...or this oxgrid thread indicates you can also use a do..while loop to populate
with values from a array holding database data.
If I substitute you combobox name,
the code would look something like:

Code:

Do While Loopcntr <= TaskListArraySizeHolder 
    DDholdName = TaskListArray(Loopcntr) 
    xlcombobox.Object.AddItem DDholdName 
    Loopcntr = Loopcntr + 1 
Loop

Also, I don't know if it will fit with what you are doing, but I've seen
some Excel code that uses shape objects to dynamically create dropdowns
as comboboxes and populate them.
This MrExcel forum thread has some example code for that approach:

Code:

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlDropDown, 10, 10, 100, 16)
lb.ControlFormat.AddItem "one",1
lb.ControlFormat.AddItem "two",2
End With

If you are wondering what the ControlFormat.AddItem parameters refer to, please see this MSDN page.
For the parameters info of the .Shape.AddFormatControl method in Excel 2007, please see this MSDN page.

Last edited by surfR2911; 06-19-2012 at 06:01 PM.

06-28-2012, 09:28 AM

dizo

Newcomer

Join Date: Jun 2012

Posts: 2

Thank you for your welcome
As you can see I was desperate, I really appreciate your help unfortunately I can resolve my problem, so I decided to configured the file in excel the buttons and the combos so when I need to use it in my program I open this file in excel and I save my changes in a new file
Thank you for take the time to answered my question.