EXEL FORMS

  sms 15:01 15 May 07
Locked

i AM USING AN EXEL FORM IN A SPREADSHEET AND HAVE A DROP DOEN MENU.
i WOULD LIKE TO PUT APPROX 50 ITEMS IN THIS DROP DOWN MENU AND THE ONLY WAY i SEE IS THAT i USE THE vba CODE .ADD ITEM "" FOR EACH ITEM.
iS THERE A QUICKER WAY FOR THE DROP DOWN BOX IN EXEL TO LOOKUP AN ARRAY

  VoG II 16:11 15 May 07

Look at Data > Data Validation > Allow > List.

  VoG II 17:07 15 May 07

An alternative is as follows for Excel 2007.

Developer > Insert > Form Controls (not ActiveX Controls) > Combo Box. Then Right click it, Format Control > Control Tab > Input Range.

Or do you want to do this in VBA?

  silverous 17:08 15 May 07

I guess his data for the drop-down may not be on the surface of the sheet ? Could always put it there I guess.

  conrail 17:19 15 May 07

bump

  sms 09:40 16 May 07

The type of form I am using is in the vba page and inserted as a form. When I right click the form I only get the properties box where I cannot define the input range ?

  VoG II 09:46 16 May 07

Is this in a Userform?

  VoG II 09:59 16 May 07

Well, if it is on a Userform, in the Visual Basic Editor you can select the combo box in the Visual Basic editor and display the properties window. Then set the row source property to refer to a range in the workbook containing the userform.

Alternatively, if you want to do this via code

UserForm1.ComboBox1.RowSource = "'Sheet1'!A1:A12"

  sms 10:17 16 May 07

Thanks VoG. Great stuff

  sms 11:20 17 May 07

Hi Vog
Tried your suggestion but did not work. I think there is VBA code already written that prevents it running eg .additem "" Do I need to delete these and replace with the code you suggested.

  VoG II 11:25 17 May 07

Yes you should remove the .AddItem code.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…