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


--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile: http://www.thecodecage.com/forumz/member.php?userid=478
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

levtweeney;407954 Wrote:
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

--------------------
Welcome to The Code Cage!, i am assuming you have checkbooxes for male
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

Simon Lloyd;407979 Wrote:
Welcome to The Code Cage!, i am assuming you have checkbooxes for male
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

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.


--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile: http://www.thecodecage.com/forumz/member.php?userid=478
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

levtweeney;408006 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

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



On Jul 8, 7:58*am, levtweeney
wrote:
Simon Lloyd;407979 Wrote: Welcome to The Code Cage!, i am assuming you have checkbooxes for male
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

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.

--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile:http://www.thecodecage.com/forumz/member.php?userid=478
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=113703

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.


On Jul 8, 11:13*am, Susan wrote:
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

On Jul 8, 7:58*am, levtweeney
wrote:



Simon Lloyd;407979 Wrote: Welcome to The Code Cage!, i am assuming you have checkbooxes for male
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

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.

--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile:http://www.thecodecage.com/forumz/member.php?userid=478
View this thread:http://www.thecodecage.com/forumz/sh....php?t=113703- Hide quoted text -

- Show quoted text -

Simon Lloyd;408235 Wrote:
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.

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 :o:

Kindest Regards

Kevin


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

--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile: http://www.thecodecage.com/forumz/member.php?userid=478
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

levtweeney;408506 Wrote:
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 :o:

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

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|
+-------------------------------------------------------------------+

--
levtweeney
------------------------------------------------------------------------
levtweeney's Profile: http://www.thecodecage.com/forumz/member.php?userid=478
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703

levtweeney;408634 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113703