Solved

Set the value selected for an ActiveX ComboBox using VBA

Posted on 2013-05-22
5
5,042 Views
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.
0
Comment
Question by:tjd71
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39190128
Hi,

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, _
                             Height:=18).Select

  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


BFN,

fp.
0
 

Author Comment

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

Expert Comment

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

Author Closing Comment

by:tjd71
ID: 39206572
Hi Fp,

Thanks :) Too easy once you know how.

Cheers
tjd71
0
 
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

828 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