Hi everyone, I've been given a task at work which needs to be done urgently so they've given it to me. I have very limited knowledge of VB programming so I'll need all the help I can get, especially with this subject.
I need to create a userform that generates a new worksheet. I know how to physically create the userform, but where I struggle is:
-Creating dropdown lists within some of the text fields on the userform
-Programming the buttons
The form looks something like this:
Select Project: (This will be a manual input)
Value: (Dropdown list with options of <250k and >250k)
Plant: (Dropdown list)
Region: (Dropdown list)
Location: (Dropdown list)
Engine Type: (Dropdown list)
IPT Lead/Buyer: (manual input)
Once the user inputs these they then press OK or DONE and ideally it should generate a worksheet for which I have a set template.
If anyone could please just provide me with a push in the right direction or help me with what the outline of the code should look like, it will be massively appreciated. If I had more time I'd just do it myself via the help section on VB but I have a strict deadline to adhere to!
Many thanks in advance!
You don't create dropdown lists in a text fields, you use a different control, either a combobox or a listbox.
These controls have events that you can use, such as
Private Sub ComboBox1_Change() 'do your stuff here End Sub
As Bob said, create a combobox from the Toolbox, then to populate that combobox you could do the following:
ComboBoxName.List = Array("NameX", "NameY", "NameZ")
So just replcae ComboBoxName with the name you give your combobox, and then the NameX, NameY etc. with the values you want in the drop-down e.g. ">250k".
For the manual inputs, just create a normal "textbox".
Once this is all done, create a command button on your UserForm and go into the code view on that.
Dim EmptyRow As Long Dim ws As Worksheet Set ws = Worksheets("YourSheetName") EmptyRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row ws.Cells(EmptyRow, 1).Value = ComboBoxName.Value ws.Cells(EmptyRow, 2).Value = TextField1.Value ws.Cells(EmptyRow, 3).Value = TextField2.Value ws.Cells(EmptyRow, 4).Value = TextField3.Value ws.Cells(EmptyRow, 5).Value = TextField4.Value
This basically adds the data from the userform into the empty rows on that spreadsheet template you've created. Let me know if you have any problems
Last edited by Toddneyx; 12-09-2010 at 11:28 AM.
Many thanks for all your help, I will try it when I'm back in the office tomorrow morning and let you know how it goes. Thanks again, really appreciate it!
@Toddneyx: This is what I've done as an example:
But when I run this and the userform pops up, the drop down list for Value is blank, it doesn't have the two values from the code. Why is this?Private Sub ComboBox1_Change() ComboBoxName.List = Array("<250k", ">250k") End Sub
Ok firstly you have to change the "ComboBoxName" to the name of your combobox, which I'm guessing is called "ComboBox1"
Then this code should be under userform_initialize()
ComboBox1.List = Array("<250k", ">250k")
Did you have any luck? Upload the spreadsheet if not
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks