Solved

How to make combo box work in Excel VBA

Posted on 2006-07-13
5
21,243 Views
Last Modified: 2012-06-27
I make a small application using Excel VBA. In the user form, I have a combo box, when user select one or more items in combo box, and then click OK button, the item details will display in another sheet that create after clicking on OK button as well..
0
Comment
Question by:linh-nguyen05
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:jagadeesh_motamarri
ID: 17105539
http://www.exceltip.com/st/Add_Combo_Box_to_a_Sheet_in_Microsoft_Excel/300.html

 Example:

The screen shot displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.


Step 1: Define names

1.Enter the months list - January-December into the cells A1 to A12 in the new worksheet.

2.Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box, and click OK.

3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.


Step 2: Add a Combo Box to a worksheet

1. Select one of the toolbars, right-click, and select the Forms toolbar.

2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.

Step 3: Format the Combo Box

1.Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab

2.In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with   an object).

3.In the Cell link box, type the name MonthNumber.

4.Click to select the 3-D shading box (more aesthetic).

5.Click OK.

Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.
0
 

Author Comment

by:linh-nguyen05
ID: 17105615
Thank you for that.

Actually, I want to use combo box in user form not in worksheet, and allow me to select one or multiple items.
 
0
 
LVL 3

Expert Comment

by:Robin888
ID: 17106422
Hi  linh-nguyen05,

Combo box only allows you to select one item. You will have to use list box to have multiple select.
Example multi select.
Add data.
Private Sub UserForm_Initialize()

End Sub
0
 
LVL 3

Accepted Solution

by:
Robin888 earned 250 total points
ID: 17106445
Hi  linh-nguyen05,

Sorry for the multipost, accidently hit enter key. Now to finish up.

To initialize list box

Private Sub UserForm_Initialize()
      With listBox
            .AddItem “Item 1”
            .AddItem “Item 2”
            .AddItem “Item 3”
            .AddItem “Item 4”
      End With
End Sub

Make sure that lstBox MultiSelect is set to “1-fmMultiSelectMulti”

Now the ok button

Private Sub cmdOK_Click()
      ActiveWorkbook.Sheets(“my test book”).Actiate
      Range(“A1”).Select
      
      For i=0 To lstBox.ListCount -1
            If lstBox.Selected(i) Then
                  ActiveCell.Value=lstBox.List(i)
                  ActiveCell.Offset(1,0).Select
End If
Next
      Range(“A1”).Select
End Sub


Selected list item will display in my test book worksheet.

Regards.
0
 

Author Comment

by:linh-nguyen05
ID: 17106619
Thank Robin

I try it now.

Linh
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now