UserForm! I'm so confused!

[loubeelou]

I've been looking around at online tutorials of how to set up a user form for my A Level ICT coursework spreadsheet, and have managed to get myself well and truly confused. I need the form to take the user's Name, Age, Gender and whether or not they are a vegetarian (using a combo box for gender and radio/option buttons for the yes/no for veggy), but i can't find how to list Blank, Male and Female in the combo box, and if I can paste the results of the UserForm into my spreadsheet page, just hidden? I really don't understand how it all works!

 

[oakwood_001]

Presuming you've already created your form, right click on it and go to 'View Code'

You need to create a procedure to "intialize" (with a z ) your form, including the population your combo box using the .AddItem property.

So for example,

Sub UserForm_Initialize()
     With cboGender
     .AddItem "Male"
     .AddItem "Female"
     End With
End Sub

Presuming you've called your Combo Box cboGender

 

[krishhi]

Hi,

This link is enough for your requirement buddy.

http://www.contextures.com/xlUserForm01.html

Have a great day

 

[loubeelou]

i'm doing that now having created my form, but how do I know where the information inputted by the user will be pasted to? i want to have it available on the same sheet so that I don't have to change some of my cell references, but I want to hide it. where will the info from this form be displayed?

 

[Smitty]

The information form your form can go anywhere you want. It's up to you on how to get it there. Here's an example of moving data to a sheet:

Private Sub CommandButton1_Click()
  Sheets("Sheet1").Range("A1").Value = cboGender.Value
End Sub

HTH,

 

[loubeelou]

Hey, sorry I'm following the website posted above, and have managed to confuse myself again. Any ideas as to what the following code means and how i should change it to refer to my spreadsheet? It keeps saying its an error :/

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Welcome Page")

thanks =]

 

[loubeelou]

ok that's started being ok now i think, but now the following code is where the problem is:

'copy the data to the database
ws.Cells(Row1, Column1).Value = enter_name_age_etc.txtname.Value
ws.Cells(Row, 2).Value = enter_name_age_etc.txtage.Value
ws.Cells(Row, 3).Value = enter_name_age_etc.cbogender.Value

ARGH!

 

[loubeelou]

Have sorted form, but having problems making the data from option buttons paste into spreadsheet. also, the combo box values are not showing up when i type in the following code

Sub enter_name_age_etc_Initialize()
     With cbogender
     .AddItem "Male"
     .AddItem "Female"
     End With

Any ideas would be very appreciated!!!!

 

[oakwood_001]

With regards to the combo box - try using UserForm_Initialize to populate the form and make sure that your Combo Box if definitely called cboGender

 

[bertie]

I have made the following assumptions
On your form you have the following controls:

Gender combobox name cboGender
Age combobox named cboAge
Radio button for yes named optYes
Radio button for no named optNo
Command button to process to form named cmdProcess
Command Button to close the form named cmdClose

When processed the form contents go into worksheet "sheet1"

'there is only ever one initialize event
Sub UserForm_Initialize()
    Dim i As Integer
 
    'populate the gender combobox
    With cboGender
        .AddItem "Male"
        .AddItem "Female"
        'set default to first value in the list
        'this way there is no need for code to handle the selection being empty
        .ListIndex = 0
    End With
 
    'populate the age combobox
    For i = 1 To 100
        With cboAge
            .AddItem i
            'set default to first value in the list
            .ListIndex = 0
        End With
    Next i
 
    'set default radio button to yes
    optYes = True
 
End Sub
 
 
 
Private Sub cmdProcess_Click()
    Dim LastRow As Long
 
    'find the last populated row on "sheet1", add one to get the next row
    Worksheets("sheet1").Activate
    LastRow = Range("a65536").End(xlUp).Row + 1
 
    'place form answers into the spreadsheet
    Cells(LastRow, 1).Value = cboGender
    Cells(LastRow, 2).Value = cboAge
 
    'yes or no?
    If optYes Then
        Cells(LastRow, 3).Value = "Yes"
    Else
        Cells(LastRow, 3).Value = "No"
    End If
 
End Sub
 
 
 
Private Sub cmdCLose_Click()
    Unload Me
End Sub