Still having trouble with my drop down box...

Posted by Deborah Morera on September 05, 2001 5:06 PM

Ok, here's what I have done..if someone could point me in the right direction I would appreciate it. I have created a dialog box that contains a drop down box (combo box) that shows the months of the year. What I would like to happen when the user selects a month is for the column containing that month's name to be copied and pasted to a new spreadsheet. How do I assign the data from the dropdown box to a variable I can use later in the code? I have tried using .list and .listindex but I get error messages so i must not understand how to use them correctly.
I am still fairly new to this VBA programming game (started learning it this weekend actually) I have managed to write code to set up new clients, I just want to add code that will print out monthly reports. Any help would be appreciated.

Thanks in advance.
Deborah

Posted by Micke A on September 07, 2001 11:46 AM

Hi,

I'm not sure what you would like to do:

To use the selected value of the combobox later you can assign a String type variable to the property "Text" in the Change-event for the Combobox

This litle example will first assign the string type variable "Myvariable" to the content of the combobox1, then cell(2,1)in the sheet named "Blad1" will be assigned the value of the variable.
This execution will happend whenever a change is made to the combobox selection.

Private Sub ComboBox1_Change()
Dim Myvariable as String
Myvariable=ComboBox1.Text
Sheets("Blad1").Cells(2, 1).Value = Myvariable
End Sub

You could also link the content of the combobox to a specific cell in a sheet by assigning a value to the property "ControlSource"
If you rightclick the combobox in designmode you can select property and view the properties window for the control. Find ControlSource and give it the value: Blad1!A2

That would assign your combobox selection to the same cell as example 1, however the change will not take effect until you close your dialogbox and give the focus back to the sheet.

Good luck with your comboboxes !
/
Micke