Ms Excel drop down box

[questionit]

Hi

Can anyone tell me how to create a drop-down box in Ms Excel please.

I simple one which can have in it some values.

Hope you understand.. i am not sure what is the difference between drop-down menu/box and combo box. but it is reuqired in ms excel.

thanks
user__786

[ADezii]

Hi

Can anyone tell me how to create a drop-down box in Ms Excel please.

I simple one which can have in it some values.

Hope you understand.. i am not sure what is the difference between drop-down menu/box and combo box. but it is reuqired in ms excel.

thanks
user__786

Within your Excel Worksheet:
__1. View ==> Toolbars ==> Control Toolbox
__2. Select and Draw a Combo Box on the Worksheet
__3. ALT+F11 to go to the Visual Basic Window
__4. In the Worksheet_Activate() Event place the following code

Private Sub Worksheet_Activate()
  ComboBox1.Clear
  ComboBox1.AddItem "Philadelphia"
  ComboBox1.AddItem "New York"
  ComboBox1.AddItem "San Francisco"
  ComboBox1.AddItem "Kansas City"
End Sub

__5. ALT+F11 to return to the Worksheet
__6. If you are still in Design Mode, press the Design Mode Icon in the Control Toolbox
__7. Activate the Worksheet where you placed the Combo Box
__8. The 4 cities will now be listed
__9. Any clarification that you may need, please let me know

[scolivas]

It might work better if you use the text validation feature. Then your user can select on the actual cell, and choose from a list.

to do this:


some where on the sheet where the user is unlikely to find it enter the options you want them to use. Be sure to either leave a blank cell, or use "select from list" in that cell so you don't have a default value that is an actual value.

Now,

• click on Data in the Menu Bar
• click on Validation...
  the wizard will open
• Select In the Settings Tab
• Select List from the Allow drop down
• to the right 2 new boxes will appear - if you used a blank in your list de-slect ignore blanks
• click the button at the end of the Source box, so you can select the list of data you entered earlier. Be sure to select the blank cell or select from list.

You can create a message for the user in the Input Message Tab and you can put an error message for the user in the Error Alert Tab - in the event they try to enter something that is not in your list.

when you are finished with the messages, click ok.

Now when the user clicks on the cell - a drop down arrow will appear, allowing them to select from the list you created.

Hope this helps
Sophie

[questionit]

Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activate()

When i press ALT+ F11 i goto VB code editor. I can see available sheets that i have on my Ms Excel Worksbook. I selct the sheet that i am currently working on.

For example, this is what i see on bar on top:
Book1.xls - Sheet1 (Code)

I have tried exacly with your method, when i come back to Excel and go out of Design mode, i see no Values in the Combo Box.

any ideas !!

Thanks

[ADezii]

Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activate()

When i press ALT+ F11 i goto VB code editor. I can see available sheets that i have on my Ms Excel Worksbook. I selct the sheet that i am currently working on.

For example, this is what i see on bar on top:
Book1.xls - Sheet1 (Code)

I have tried exacly with your method, when i come back to Excel and go out of Design mode, i see no Values in the Combo Box.

any ideas !!

Thanks

After pressiing ALT + F11, press the F7 key in case the Code Window is not visible. You will see 2 Combo Boxes at the Top of the Window. In the left Combo select Worksheet and in the right Combo select Activate. You should now be in the Activate Event of the current Worksheet.

[questionit]

Hi Adezii

I have done exactly how you have exaplined. But when i go back to Excel and come out of Design mode and check the combox box - there is no listing of items. It is empty as before.

this is the code i have put:
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Philadelphia"
ComboBox1.AddItem "New York"
ComboBox1.AddItem "San Francisco"
ComboBox1.AddItem "Kansas City"
End Sub

And i also save file after putting this code in.

Anything i might have been doing wrong?

Also, i was wondering, can we not do this in the ComboBox1 properties instead of Worksheet properties?

Thanks

[questionit]

Hello any answer please ?

[ADezii]

Hi Adezii

I have done exactly how you have exaplined. But when i go back to Excel and come out of Design mode and check the combox box - there is no listing of items. It is empty as before.

this is the code i have put:
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Philadelphia"
ComboBox1.AddItem "New York"
ComboBox1.AddItem "San Francisco"
ComboBox1.AddItem "Kansas City"
End Sub

And i also save file after putting this code in.

Anything i might have been doing wrong?

Also, i was wondering, can we not do this in the ComboBox1 properties instead of Worksheet properties?

Thanks

Replace ComboBox1 with your Combo Box Name.

[questionit]

Hello ADezii

I have put my Combox box name corrctly. Still no success.

Could you send me a sample working code please : [e-mail]

Thanks

[questionit]

Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot

[ADezii]

Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot

Private Sub ComboBox1_Change()
  Cells(10, 6).Value = ComboBox1.Value
End Sub

[questionit]

Private Sub ComboBox1_Change()
  Cells(10, 6).Value = ComboBox1.Value
End Sub

this is fine but i need to set different values e.g:

If ComboBox1.Value = "New York"
Cells(10,6).Value = "Any value"

..

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this.

Thanks

[questionit]

this is fine but i need to set different values e.g:

If ComboBox1.Value = "New York"
Cells(10,6).Value = "Any value"

..

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this.

Thanks

any help please ?

[ADezii]

Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot

Compiling the code is not the issue - you must re-activate the WorkSheet in order for the Combo Box to populate.

[questionit]

Compiling the code is not the issue - you must re-activate the WorkSheet in order for the Combo Box to populate.

Ok,thanks.

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this. E.g:
If combox.Value = New york" THEN
Cells(10,5).Value = 100
....
i dont knowVB programming.

Thanks

[SammyB]

i dont knowVB programming.

Then it would be easier to not use code. If you use Data Validation as Scolivas suggested, you do not need any code. Try this simple example:

1. Open a new Excel workbook and enter New York, Chicago, & Hong Kong in A1, A2 & A3. Also enter the numbers associated with these cities, say, 100, 50 & 30 in B1, B2 & B3.
2. Click on A10. Menu: Data, Validation. Allow:List. Source: =A1:A3
3. Press OK
4. Click on the drop-down arrow and select New York.
5. Click on B10 and enter this formula: =VLOOKUP(A10,DB,2,FALSE)

Isn't that easier than programming?

[questionit]

Then it would be easier to not use code. If you use Data Validation as Scolivas suggested, you do not need any code. Try this simple example:

1. Open a new Excel workbook and enter New York, Chicago, & Hong Kong in A1, A2 & A3. Also enter the numbers associated with these cities, say, 100, 50 & 30 in B1, B2 & B3.
2. Click on A10. Menu: Data, Validation. Allow:List. Source: =A1:A3
3. Press OK
4. Click on the drop-down arrow and select New York.
5. Click on B10 and enter this formula: =VLOOKUP(A10,DB,2,FALSE)

Isn't that easier than programming?

Adezzi

Thanks, that is easy but i want to do it in programming. I have knowledge of C/C++ so it would be much easier and consistent if i learn this in VB.
Can you tell me how to do that in VB

Thanks

[ADezii]

Ok,thanks.

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this. E.g:
If combox.Value = New york" THEN
Cells(10,5).Value = 100
....
i dont knowVB programming.

Thanks

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
  Case "New York"
    Cells(1, 2).Value = "You chose New York"
  Case "Philadelphia"
    Cells(3, 2).Value = "You chose Philadelphia"
  Case "San Francisco"
    Cells(5, 2).Value = "You chose San Francisco"""
  Case "Kansas City"
    Cells(7, 2).Value = "You chose Kansas City"
End Select
 End Sub

[questionit]

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
  Case "New York"
    Cells(1, 2).Value = "You chose New York"
  Case "Philadelphia"
    Cells(3, 2).Value = "You chose Philadelphia"
  Case "San Francisco"
    Cells(5, 2).Value = "You chose San Francisco"""
  Case "Kansas City"
    Cells(7, 2).Value = "You chose Kansas City"
End Select
 End Sub

Thanks a lot Adezzi