Hi all,
I literally have about 4 days of VBA experience so please bear with me.
I'm having problems creating List/Combo boxes using a macro. Devising
a macro which, on execution prompts the user for the location of a
source excel file containing the raw data. This I've done using
Application.GetOpenFilename and it works. Once the file is selected I
need the user to promted by two dialog boxes, one after the other.

1) First dialog box presents the user with a drop down list containing
entries for the 12 months ("January", "February"...."December"). The
Dialog box comes with an OK and Cancel button.

2) Second dialog box presents the user with a drop down list containing
entries for years in the future upto 2010 ("2006", "2007"...."2010").
The Dialog box again comes with an OK and Cancel button.

The macro will then identify which values have been selected,
concatenate these as one string value and populate a Cell (assume Cell
A5) with this value thus making it the title. Finally contents of a
sheet from the source file and copied and pasted to Workbook where the
application is running from.

Please help on how to create such drop down lists as I keep trying to
create a ComboBox the same was as InputBox or MsgBox and that does not
seem to work. Desperately need help.

Thank you kindly
Khurram

Appreciate the time John, I will check this and get back to you.

Cheers
Deecrypt
John Bundy wrote:

Right click your VBA Project inside Visual Basic and select Insert-UserForm
Add to the form a label for the name, a combobox, and 2 buttons, size and
create more as needed. Change the Caption property on the buttons to OK and
Select. Change the Userform name and caption to something intelligent such as
SelectMonth, no spaces in the name but ok to do so in the caption. Repeat
this process for a second form and name SelectYear. On both forms you can
double click the Cancel button and add whatever code you would like to end
the processing. Double clicking on the OK button set a variable equal to the
selection e.g. selectedMonth = SelectMonth.ComboBox1.Value
Repeat for your other form e.g selectedYear = SelectYear.ComboBox1.Value.
Make sure that you have code that when you hit the buttons it will remove the
forms e.g SelectMonth.Hide. Now you can concantenate those values and place
in say cell A1 with Cells(1,1)=selectedMonth &", "& selectedYear.

Not forgetting the important part, under the userform activate we need to
populate the comboboxes:
Private Sub UserForm_Activate()
selectMonth.ComboBox1.AddItem "January"
selectMonth.ComboBox1.AddItem "February"
selectMonth.ComboBox1.AddItem "March"
selectMonth.ComboBox1.AddItem "April"
selectMonth.ComboBox1.AddItem "May"
selectMonth.ComboBox1.AddItem "June"
selectMonth.ComboBox1.AddItem "July"
selectMonth.ComboBox1.AddItem "August"
selectMonth.ComboBox1.AddItem "September"
selectMonth.ComboBox1.AddItem "October"
selectMonth.ComboBox1.AddItem "November"
selectMonth.ComboBox1.AddItem "December"
End Sub

And

Private Sub UserForm_Activate()
selectYear.ComboBox1.AddItem "2006"
selectYear.ComboBox1.AddItem "2007"
selectYear.ComboBox1.AddItem "2008"
selectYear.ComboBox1.AddItem "2009"
selectYear.ComboBox1.AddItem "2010"
selectYear.ComboBox1.AddItem "2011"
selectYear.ComboBox1.AddItem "2012"

End Sub

Let me know how it works and post any more questions

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Deecrypt" wrote:

Hi all,
I literally have about 4 days of VBA experience so please bear with me.
I'm having problems creating List/Combo boxes using a macro. Devising
a macro which, on execution prompts the user for the location of a
source excel file containing the raw data. This I've done using
Application.GetOpenFilename and it works. Once the file is selected I
need the user to promted by two dialog boxes, one after the other.

1) First dialog box presents the user with a drop down list containing
entries for the 12 months ("January", "February"...."December"). The
Dialog box comes with an OK and Cancel button.

2) Second dialog box presents the user with a drop down list containing
entries for years in the future upto 2010 ("2006", "2007"...."2010").
The Dialog box again comes with an OK and Cancel button.

The macro will then identify which values have been selected,
concatenate these as one string value and populate a Cell (assume Cell
A5) with this value thus making it the title. Finally contents of a
sheet from the source file and copied and pasted to Workbook where the
application is running from.

Please help on how to create such drop down lists as I keep trying to
create a ComboBox the same was as InputBox or MsgBox and that does not
seem to work. Desperately need help.

Thank you kindly
Khurram

Hi John,
The code works like a charm. You have opened up a whole new avenue of
things I can do with a macro now. I'll be in touch if I require any
more help (which is more than likely :-)

Cheers
Khurram

Deecrypt wrote:

Appreciate the time John, I will check this and get back to you.

Cheers
Deecrypt
John Bundy wrote:

Right click your VBA Project inside Visual Basic and select Insert-UserForm
Add to the form a label for the name, a combobox, and 2 buttons, size and
create more as needed. Change the Caption property on the buttons to OK and
Select. Change the Userform name and caption to something intelligent such as
SelectMonth, no spaces in the name but ok to do so in the caption. Repeat
this process for a second form and name SelectYear. On both forms you can
double click the Cancel button and add whatever code you would like to end
the processing. Double clicking on the OK button set a variable equal to the
selection e.g. selectedMonth = SelectMonth.ComboBox1.Value
Repeat for your other form e.g selectedYear = SelectYear.ComboBox1.Value.
Make sure that you have code that when you hit the buttons it will remove the
forms e.g SelectMonth.Hide. Now you can concantenate those values and place
in say cell A1 with Cells(1,1)=selectedMonth &", "& selectedYear.

Not forgetting the important part, under the userform activate we need to
populate the comboboxes:
Private Sub UserForm_Activate()
selectMonth.ComboBox1.AddItem "January"
selectMonth.ComboBox1.AddItem "February"
selectMonth.ComboBox1.AddItem "March"
selectMonth.ComboBox1.AddItem "April"
selectMonth.ComboBox1.AddItem "May"
selectMonth.ComboBox1.AddItem "June"
selectMonth.ComboBox1.AddItem "July"
selectMonth.ComboBox1.AddItem "August"
selectMonth.ComboBox1.AddItem "September"
selectMonth.ComboBox1.AddItem "October"
selectMonth.ComboBox1.AddItem "November"
selectMonth.ComboBox1.AddItem "December"
End Sub

And

Private Sub UserForm_Activate()
selectYear.ComboBox1.AddItem "2006"
selectYear.ComboBox1.AddItem "2007"
selectYear.ComboBox1.AddItem "2008"
selectYear.ComboBox1.AddItem "2009"
selectYear.ComboBox1.AddItem "2010"
selectYear.ComboBox1.AddItem "2011"
selectYear.ComboBox1.AddItem "2012"

End Sub

Let me know how it works and post any more questions

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Deecrypt" wrote:

Hi all,
I literally have about 4 days of VBA experience so please bear with me.
I'm having problems creating List/Combo boxes using a macro. Devising
a macro which, on execution prompts the user for the location of a
source excel file containing the raw data. This I've done using
Application.GetOpenFilename and it works. Once the file is selected I
need the user to promted by two dialog boxes, one after the other.

1) First dialog box presents the user with a drop down list containing
entries for the 12 months ("January", "February"...."December"). The
Dialog box comes with an OK and Cancel button.

2) Second dialog box presents the user with a drop down list containing
entries for years in the future upto 2010 ("2006", "2007"...."2010").
The Dialog box again comes with an OK and Cancel button.

The macro will then identify which values have been selected,
concatenate these as one string value and populate a Cell (assume Cell
A5) with this value thus making it the title. Finally contents of a
sheet from the source file and copied and pasted to Workbook where the
application is running from.

Please help on how to create such drop down lists as I keep trying to
create a ComboBox the same was as InputBox or MsgBox and that does not
seem to work. Desperately need help.

Thank you kindly
Khurram