Option Button in User Form

[levtweeney]

Hi all

I am creating a user form to add data to a spreadsheet. I have set up
text boxes to add this data and then move on to the next line ready for
new data (This works). I also need to allow this to happen when a user
selects an option box (Male or Female).

I have create a frame (Frame1) and placed two option boxes within this
frame, one called optMale and the other optFemale, these have also been
grouped.

This is the code I currently have on my command button, which allows me
to add the data to the spreadsheet but I need to add the gender for each
row from these option boxes. Any help would be most appreciated.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
End(xlUp).Offset(1, 0).Row

'copy the data to the Spreadsheet
ws.Cells(iRow, 1).Value = Me.houseNo.Value
ws.Cells(iRow, 2).Value = Me.streetName.Value
ws.Cells(iRow, 3).Value = Me.Town.Value
ws.Cells(iRow, 4).Value = Me.postCode.Value

'clear the data
Me.houseNo.Value = ""
Me.streetName.Value = ""
Me.Town.Value = ""
Me.postCode.Value = ""


End Sub

 

[Simon Lloyd]

Hi all

I am creating a user form to add data to a spreadsheet. I have set up
text boxes to add this data and then move on to the next line ready for
new data (This works). I also need to allow this to happen when a user
selects an option box (Male or Female).

I have create a frame (Frame1) and placed two option boxes within this
frame, one called optMale and the other optFemale, these have also been
grouped.

This is the code I currently have on my command button, which allows me
to add the data to the spreadsheet but I need to add the gender for each
row from these option boxes. Any help would be most appreciated.
Code:
--------------------
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the Spreadsheet
ws.Cells(iRow, 1).Value = Me.houseNo.Value
ws.Cells(iRow, 2).Value = Me.streetName.Value
ws.Cells(iRow, 3).Value = Me.Town.Value
ws.Cells(iRow, 4).Value = Me.postCode.Value

'clear the data
Me.houseNo.Value = ""
Me.streetName.Value = ""
Me.Town.Value = ""
Me.postCode.Value = ""


End Sub
--------------------

or female?

Code:
--------------------
If Me.CheckBox1.Value = True Then
Range("A1").Value = "Male"
ElseIf Me.CheckBox2.Value = True Then
Range("A1").Value = "Female"
End If
--------------------
naturally you would change Range("A1") for ws.Cells(iRow, x).Value where
x would be your next location


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[levtweeney]

Welcome to The Code Cage!, i am assuming you have checkbooxes for male
or female? Code:
--------------------
Range("A1").Value = "Male"
ElseIf Me.CheckBox2.Value = True Then
Range("A1").Value = "Female"
End If --------------------
where x would be your next location

Hi Simon

Thank you so much for the reply, I really do appreciate it.

I have a couple of questions. Why did you put Checkbox1 and Checkbox2
in the code? Should I use “optMale” and “optFemale” as these are the
names of my option box's? Have I added the range correctly?

If Me.optMale.Value = True Then
Range ws.Cells(iRow, x).Value = "Male"
ElseIf Me.optFemale.Value = True Then
Range ws.Cells(iRow, x).Value = "Female"
End If


When I run the above code I get an "Invalid use of property" message

I am sorry for being so silly but I really am an amateur at all of
this.

 

[Simon Lloyd]

Hi Simon

Thank you so much for the reply, I really do appreciate it.

I have a couple of questions. Why did you put Checkbox1 and Checkbox2
in the code? Should I use “optMale” and “optFemale” as these are the
names of my option box's? Have I added the range correctly?
Code:
--------------------
If Me.optMale.Value = True Then
Range ws.Cells(iRow, x).Value = "Male"
ElseIf Me.optFemale.Value = True Then
Range ws.Cells(iRow, x).Value = "Female"
End If
--------------------

When I run the above code I get an "Invalid use of property" message

I am sorry for being so silly but I really am an amateur at all of
this.Firstly i used checkbox1..etc as an example, you didn't say what the

boxes were, if you have used optMale etc then use those (as long as they
are checkboxes), you have used x in your range, i explained that x would
be your next location so you probably want
Code:
--------------------
Range ws.Cells(iRow, 5).Value
--------------------
but without seeing your workbook or structure i couldn't give you a
definite answer.


Attachments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attachments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Susan]

hi -
using option buttons (optMale & optFemale) is good because only one
can be selected at any time, unlike checkboxes in which both could be
checked at the same time.
if this section of code is in your userform code, i believe your
problem is in the use of the keyword Me. you're already "in" Me, so
you don't have to use it. try taking that out and using your optMale
and optFemale as you did originally.

If optMale.Value = True Then
Range ws.Cells(iRow, x).Value = "Male"
ElseIf optFemale.Value = True Then
Range ws.Cells(iRow, x).Value = "Female"
End If

i may be wrong, but it's a try.
if this portion of code is in a regular module, then you'd need to use
"Userform1" in front of optMale and optFemale.
susan

 

[Susan]

sigh. that's what i get for not reading the whole post - you're not
talking about userforms at all, it seems your option buttons are on
the worksheet.

i designed a small test and this works with option buttons as you have
indicated:

Private Sub CommandButton1_Click()

If Me.optMale.Value = True Then
MsgBox "It's Male"
ElseIf Me.optFemale.Value = True Then
MsgBox "It's Female"
End If

End Sub

so it's not the option buttons that are your problem.
as for your ranges,

Range ws.Cells(iRow, x).Value = "Male"

i don't see where you are defining iRow or x. that may be your error.


susan, shutting up now.

 

[levtweeney]

Firstly i used checkbox1..etc as an example, you didn't say what the
boxes were, if you have used optMale etc then use those (as long as they
are checkboxes), you have used x in your range, i explained that x would
be your next location so you probably want > Code:
--------------------
definite answer.

Hi again Simon

I have attached the excel file for you (Or anyone else who can help)
to have a look at if you have the time.

I am trying to allow users to fill in the form and then press enter,
which adds that record on to the row under the headings. The form
clears and users can add a new record, which will enter on the next
line down.

Sorry to be a pain everyone just trying to learn :

Kindest Regards

Kevin

+-------------------------------------------------------------------+
|Filename: Example user form.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=173|
+-------------------------------------------------------------------+

 

[Simon Lloyd]

Hi again Simon

I have attached the excel file for you (Or anyone else who can help) to
have a look at if you have the time.

I am trying to allow users to fill in the form and then press enter,
which adds that record on to the row under the headings. The form clears
and users can add a new record, which will enter on the next line down.

Sorry to be a pain everyone just trying to learn :

Kindest Regards

KevinHere you go, this works, they're not boxes but radio buttons, you also

missed the age box, all fixed!
Code:
--------------------
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the Spreadsheet
ws.Cells(iRow, 1).Value = Me.houseNo.Value
ws.Cells(iRow, 2).Value = Me.streetName.Value
ws.Cells(iRow, 3).Value = Me.Town.Value
ws.Cells(iRow, 4).Value = Me.postCode.Value
ws.Cells(iRow, 5).Value = Me.TextBox1.Value
If Me.optMale.Value = True Then
Me.optFemale.Value = False
ws.Cells(iRow, 6).Value = "Male"
ElseIf Me.optFemale.Value = True Then
Me.optMale.Value = False
ws.Cells(iRow, 6).Value = "Female"
End If
'clear the data
Me.houseNo.Value = ""
Me.streetName.Value = ""
Me.Town.Value = ""
Me.postCode.Value = ""
Me.TextBox1.Value = ""
Me.optFemale.Value = False
Me.optMale.Value = False

End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[levtweeney]

Hi again Simon

I have attached the excel file for you (Or anyone else who can help) to
have a look at if you have the time.

I am trying to allow users to fill in the form and then press enter,
which adds that record on to the row under the headings. The form clears
and users can add a new record, which will enter on the next line down.

Sorry to be a pain everyone just trying to learn

Kindest Regards

Kevin


+-------------------------------------------------------------------+
|Filename: Example user form.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=174|
+-------------------------------------------------------------------+

 

[Simon Lloyd]

Hi again Simon

I have attached the excel file for you (Or anyone else who can help) to
have a look at if you have the time.

I am trying to allow users to fill in the form and then press enter,
which adds that record on to the row under the headings. The form clears
and users can add a new record, which will enter on the next line down.

Sorry to be a pain everyone just trying to learn

Kindest Regards

KevinWhy have you repeated your post?, i supplied the fixed code in the above

post!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)