• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

Excel - insert a control using VB

I create a work sheet at run time, inserting text and formulae, but I do not know how to insert a combo box into a cell and fill it with values for the user to select.

TIA

John, UK
0
JohnHind
Asked:
JohnHind
  • 2
1 Solution
 
SubhasundarCommented:
dear JohnHind,

Just try out this following piece of code:
Make these declarations in a module:
Dim db As Object
Dim wb As Object
Dim ws As Object

Then write the following piece of code( which I have inserted in the form load event):

Private Sub Form_Load()

Set db = CreateObject("Excel.Application")
db.Visible = True

Set wb = db.Workbooks.Add(1)
Set ws = wb.Worksheets.Add
ws.Activate

ws.dropdowns.Add(96.75, 115.5, 47.25, 12).Select


End Sub


Hope it works.
0
 
SubhasundarCommented:
I have modified the code a bit. It is like this:

Private Sub Form_Load()

Set db = CreateObject("Excel.Application")
db.Visible = True

Set wb = db.Workbooks.Add(1)
Set ws = wb.Worksheets.Add
ws.Activate
Dim cb As Object
Set cb = ws.dropdowns.Add(96.75, 115.5, 47.25, 12)
'With selection
   cb.AddItem "subha", 1 ' first value to add
'End With
   

End Sub
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now