Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up drop down combo box with VBA
In Excel 97 under WinXP pro, I’m trying to create a combo box where a
user can select from a set of options or input a custom entry when he adds a record to an spreadsheet set up as a data base. The location and output of the dialog box must move down with each newly added row. Using the “forms” toolbar, I have created drop down lists from which the user could select options, but the canned dialog box tools do not seem to allow for automatically repositioning the dialog box with the addition of each new record. Furthermore, both the list box and the combo box do not seem allow a custom entry; they both return just the number of the row of the selected entry. I’ve also tried using Visual Basic to create a list box. Starting in the VBA editor, I create a form (UserForm1) and a ListBox (ListBox1) within the user form. I then tried running the following macro and numerous variations on the theme based in the example given in Peter Wright’s “Beginning Visual Basic 6.” The VBA editor seems to recognize the code as it automatically corrects the spelling and capitalization of the various terms, but when I run it (press F5), an empty dialog box appears on the spread sheet. The macro code looks like. Private Sub Form_Load() ListBox1.AddItem “test1” ListBox1.AddItem “test2” End Sub Can someone point me to more information on the subject. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up drop down combo box with VBA
I think your code is in the wrong place.
In a standard code module put this: SUb ShowForm() Userform1.Show End Sub Select the userform aclled userform1 and go to its code sheet. Add these: Private Sub UserForm_Initialize() with listbox1 .AddItem "A" .Additem "B" ' ....etc end with End Sub to grab data from a sheet instead, assuming your data is in a range named "MyData" Private Sub UserForm_Initialize() Dim cell As Range With ListBox1 For Each cell In Names.Item("MyData").RefersToRange .AddItem cell.Value Next End With End Sub HTH "windsurferLA" wrote: In Excel 97 under WinXP pro, Im trying to create a combo box where a user can select from a set of options or input a custom entry when he adds a record to an spreadsheet set up as a data base. The location and output of the dialog box must move down with each newly added row. Using the €śforms€ť toolbar, I have created drop down lists from which the user could select options, but the canned dialog box tools do not seem to allow for automatically repositioning the dialog box with the addition of each new record. Furthermore, both the list box and the combo box do not seem allow a custom entry; they both return just the number of the row of the selected entry. Ive also tried using Visual Basic to create a list box. Starting in the VBA editor, I create a form (UserForm1) and a ListBox (ListBox1) within the user form. I then tried running the following macro and numerous variations on the theme based in the example given in Peter Wrights €śBeginning Visual Basic 6.€ť The VBA editor seems to recognize the code as it automatically corrects the spelling and capitalization of the various terms, but when I run it (press F5), an empty dialog box appears on the spread sheet. The macro code looks like. Private Sub Form_Load() ListBox1.AddItem €śtest1€ť ListBox1.AddItem €śtest2€ť End Sub Can someone point me to more information on the subject. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up drop down combo box with VBA
Windsurfer,
That's the trouble with reading a VB6 book and then trying to apply to VBA :-). In VB, when a form is loaded, it triggers the Form_Load event. But in VBA, it triggers the Userform_Initialize event. Try Private Sub Userform_Initialize() ListBox1.AddItem “test1” ListBox1.AddItem “test2” End Sub -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... In Excel 97 under WinXP pro, I’m trying to create a combo box where a user can select from a set of options or input a custom entry when he adds a record to an spreadsheet set up as a data base. The location and output of the dialog box must move down with each newly added row. Using the “forms” toolbar, I have created drop down lists from which the user could select options, but the canned dialog box tools do not seem to allow for automatically repositioning the dialog box with the addition of each new record. Furthermore, both the list box and the combo box do not seem allow a custom entry; they both return just the number of the row of the selected entry. I’ve also tried using Visual Basic to create a list box. Starting in the VBA editor, I create a form (UserForm1) and a ListBox (ListBox1) within the user form. I then tried running the following macro and numerous variations on the theme based in the example given in Peter Wright’s “Beginning Visual Basic 6.” The VBA editor seems to recognize the code as it automatically corrects the spelling and capitalization of the various terms, but when I run it (press F5), an empty dialog box appears on the spread sheet. The macro code looks like. Private Sub Form_Load() ListBox1.AddItem “test1” ListBox1.AddItem “test2” End Sub Can someone point me to more information on the subject. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up drop down combo box with VBA
I tried what you suggested with many variations and at first it seemed
like no matter what I did, I could not load a list box with entries. Then, all of a sudden, it started working. I’m not sure why. Perhaps it started working after I clicked on the list box in the form window. The clicking action automatically opened a template macro in the UserForm1(code) sheet. Since then I’ve learned how to read which item in the list box was selected and to hide the box after the selection is made. My problem may have been that I had been concentrating on loading text into a combo box rather than a list box. When I would run the macro, the combo box would pop-up, but it would appear empty. It was not until accidentally clicked on the down arrow that I realized clicking the arrow was essential to getting the list to appear in the combo box. Thanks for the help. Bob Phillips wrote: Windsurfer, That's the trouble with reading a VB6 book and then trying to apply to VBA :-). In VB, when a form is loaded, it triggers the Form_Load event. But in VBA, it triggers the Userform_Initialize event. Try Private Sub Userform_Initialize() ListBox1.AddItem “test1” ListBox1.AddItem “test2” End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting up drop down combo box with VBA
Set the Listindex property to 1
-- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... I tried what you suggested with many variations and at first it seemed like no matter what I did, I could not load a list box with entries. Then, all of a sudden, it started working. I’m not sure why. Perhaps it started working after I clicked on the list box in the form window. The clicking action automatically opened a template macro in the UserForm1(code) sheet. Since then I’ve learned how to read which item in the list box was selected and to hide the box after the selection is made. My problem may have been that I had been concentrating on loading text into a combo box rather than a list box. When I would run the macro, the combo box would pop-up, but it would appear empty. It was not until accidentally clicked on the down arrow that I realized clicking the arrow was essential to getting the list to appear in the combo box. Thanks for the help. Bob Phillips wrote: Windsurfer, That's the trouble with reading a VB6 book and then trying to apply to VBA :-). In VB, when a form is loaded, it triggers the Form_Load event. But in VBA, it triggers the Userform_Initialize event. Try Private Sub Userform_Initialize() ListBox1.AddItem “test1” ListBox1.AddItem “test2” End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box/Drop down box | Excel Discussion (Misc queries) | |||
Using Drop Down List or Combo Box | Excel Worksheet Functions | |||
Drop Down List Box, Combo Box | Excel Discussion (Misc queries) | |||
Combo Box Drop Down Area | Excel Programming | |||
Setting the value of a cell from a combo box | Excel Programming |