Set the value selected for an ActiveX ComboBox using VBA

Posted on 2013-05-22
Medium Priority
Last Modified: 2013-05-30
I am trying to set the value selected for an ActiveX Control Box using VBA to the first item in the list or blank. When recording the macro no code is produced for the action.
Question by:tjd71
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 35

Accepted Solution

[ fanpages ] earned 2000 total points
ID: 39190128

Set the ListIndex property of the ComboBox to 0 (zero).

For example:
Option Explicit
Public Sub Create_ComboBox1()

  ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                             Link:=False, _
                             DisplayAsIcon:=False, _
                             Left:=50, _
                             Top:=50, _
                             Width:=100, _

  ActiveSheet.OLEObjects("ComboBox1").Object.AddItem "First"
  ActiveSheet.OLEObjects("ComboBox1").Object.AddItem "Second"
  ActiveSheet.OLEObjects("ComboBox1").Object.AddItem "Third"
  ActiveSheet.OLEObjects("ComboBox1").Object.AddItem "Fourth"
  ActiveSheet.OLEObjects("ComboBox1").Object.AddItem "Fifth"
  ActiveSheet.OLEObjects("ComboBox1").Object.ListIndex = 0&     ' Select the first item
End Sub

Open in new window



Author Comment

ID: 39198295
Thanks FP, I am away from the office until Thursday and will try it then.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39198635
OK.  Thanks for letting me know.

Author Closing Comment

ID: 39206572
Hi Fp,

Thanks :) Too easy once you know how.

LVL 35

Expert Comment

by:[ fanpages ]
ID: 39206908
:) You're welcome.

Sometimes it is the simplest of things that become the most difficult to discover!

Good luck with the rest of your project.

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question