Solved

How to make combo box work in Excel VBA

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

726 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