[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to make combo box work in Excel VBA

Posted on 2006-07-13
5
Medium Priority
?
21,252 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Introduction to Processes
Screencast - Getting to Know the Pipeline

873 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