Combobox in a worksheet

[dRAMmer]

Hi to all!

I've been searching on this forum topic for what I want to create for almost an hour now. All I found is using a combox with a userform, what I want is to create/include a combox into a worksheet. It's like when you have a list created from "Data->List->Create List.." menu. This combox will contain data coming from sql server.

Thanks in advance!

[RobDog888]

Wehn ever you need to know how to do something in VBA, just record a macro doing your needed task and then view the Module code generated. Although, I dont really recommend using the ActiveCell object but you get the idea.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/13/2006 by VB/Office Guru
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlNo).Name = "List1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "red"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "blue"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "ttt"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "aaa"
    Range("A1:A6").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

[dRAMmer]

Hi Rob!

Thanks for the quick reply. I have done already trying to see if I can make something out of it. But my problem remains that I can't create a "dynamic" combox out of it. If try to create it into a list then the connection will be lost. Here is what I'm trying to do:

- Create a combo box on a sheet
- Populate it with data from a select statement
- Have another combobox whose values depends or is filtered out by the value given by the first combobox. values will also be coming from database.

Can you give an example about this.

[RobDog888]

i think so. Does the combo's need to be like the filtering or sorting ones or can it be an ActiveX combo box?

[dRAMmer]

As much as possible I'd like to shy off from activeX, you see I have something similar done w/Smart Doc. But as you know, smart docs are hard to deploy. So I want a simple one, where users can just open the workbook and start typing.

The control is more of a filtering type.

[dRAMmer]

Anybody there who can help? I would really appreciate it. Thanks in advance!

[RobDog888]

Sorry for the delay, dont worry about the ActiveX combo box as its part of VBA so no other dependancies are needed that Excel doesnt already need.

I suggest using that one since its so similar to the VB 6 Combo box control.

[dRAMmer]

Thanks for the reply, how about the sample code I was asking, can you give me a code I can start with. Thanks again.

[RobDog888]

Adds 10 items to a ComboBox1 on Sheet1.

Option Explicit
 
Private Sub Workbook_Open()
    Dim i As Integer
    For i = 1 To 10
        Workbooks(1).Sheets("Sheet1").OLEObjects("ComboBox1").Object.AddItem "Item " & i
    Next
End Sub

[dRAMmer]

I have managed to insert a combobox on the sheet then pasted your code on Workbook_Open event and got this error: Application-defined or object-defined error

[RobDog888]

Does your combo box reside on "Sheet1" and the cbo is named "ComboBox1"?
Also, macros enabled?

[dRAMmer]

Hi Rob!

Yes is the answer to all your question. I had solved it now and I'd like to know why do I have to save the workbook first to be able not to get that error? I'd prefer a technical explanation about that, something like an answer from "excel documentation".

Thanks so much

[RobDog888]

I just pasted my code into a new workbook and added a cbo then ran the workbook_open procedure and it populated just fine.

[dRAMmer]

I've created a new workbook wrote a simple debug.print code in Workbook_Open and nothing is happening, I really have to save it first for the code to run. Anyway, thanks a lot, you've been a great help, problem solved!

[Bruce Fox]

Although this is resolved; as an alternate you could use "Create List" for a select range, then dynamically add values. No CBO overhead. - Just a thought

[RobDog888]

Workbook_Open only runs upon the opening or a workbook. If you manually run my code in post #9 on a new unsaved workbook with a combo it populates it just fine. Maybe a bit more of when you need it populated?