Solved

How to make combo box work in Excel VBA

Posted on 2006-07-13
5
21,245 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
splitOdd10 challenge 5 116
Regular Expression Calculator Tester 2 80
How to get time difference in minutes and seconds only between 2 dates 2 46
Create .bat File 16 29
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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.
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 …

840 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