?
Solved

How to make combo box work in Excel VBA

Posted on 2006-07-13
5
Medium Priority
?
21,248 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
[X]
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
  • 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 1000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
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…
Simple Linear Regression
Suggested Courses

770 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