Frustrated with learning add command code...
 

I have completed a userform with the following fields:

FirstName LastName Phone Email Address City State Zip

I would like to have a command button that adds these to my worksheet.

I would also like to add check boxes so they can select any initerests they
may have like transportaion, schools, safety. These can show up as an x in
the cell if selected and blank if not.

I have read and reread posts, help, goolgled them all and am still having
trouble learning how to do this. Can anybody help me with the code, or
point me to example add command codes?

I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.

Thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") ****getting runtime error "9"
subscript out of range error here.
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value


'clear the data
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
Me.txtAddress.SetFocus
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""

End Sub

Frustrated with learning add command code...
 

Confirm the spelling of the worksheet tab name. Look out for blank spaces at
the end o fthe name... Just like in school spelling counts...
--
HTH...

Jim Thomlinson


"Stacy" wrote:

I have completed a userform with the following fields:

FirstName LastName Phone Email Address City State Zip

I would like to have a command button that adds these to my worksheet.

I would also like to add check boxes so they can select any initerests they
may have like transportaion, schools, safety. These can show up as an x in
the cell if selected and blank if not.

I have read and reread posts, help, goolgled them all and am still having
trouble learning how to do this. Can anybody help me with the code, or
point me to example add command codes?

I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.

Thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") ****getting runtime error "9"
subscript out of range error here.
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value


'clear the data
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
Me.txtAddress.SetFocus
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""

End Sub

Frustrated with learning add command code...
 

stacy - you did a lot all on your own!!!!
try adding this:

Dim wb as Workbook

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("PersonalInfoUpdate")

since you're using a userform, you have to specify the workbook and
all.
hope that helps some!
:)
susan




On Jul 30, 3:56*pm, Stacy wrote:
I have completed a userform with the following fields:

FirstName * * * LastName * * * *Phone * Email * Address City * *State * Zip

I would like to have a command button that adds these to my worksheet. *

I would also like to add check boxes so they can select any initerests they
may have like transportaion, schools, safety. *These can show up as an x in
the cell if selected and blank if not.

I have read and reread posts, help, goolgled them all and am still having
trouble learning how to do this. * Can anybody help me with the code, or
point me to example add command codes?

I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.

Thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") * * ****getting runtime error "9"
subscript out of range error here.
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
* .End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value

'clear the data
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
Me.txtAddress.SetFocus
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""

End Sub

Frustrated with learning add command code...
 

Susan,

I am still getting the runtime error and I triple checked everthing. Also,
still can't find anything about the check box commands.

Thanks!

Stacy

"Susan" wrote:

stacy - you did a lot all on your own!!!!
try adding this:

Dim wb as Workbook

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("PersonalInfoUpdate")

since you're using a userform, you have to specify the workbook and
all.
hope that helps some!
:)
susan




On Jul 30, 3:56 pm, Stacy wrote:
I have completed a userform with the following fields:

FirstName LastName Phone Email Address City State Zip

I would like to have a command button that adds these to my worksheet.

I would also like to add check boxes so they can select any initerests they
may have like transportaion, schools, safety. These can show up as an x in
the cell if selected and blank if not.

I have read and reread posts, help, goolgled them all and am still having
trouble learning how to do this. Can anybody help me with the code, or
point me to example add command codes?

I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.

Thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") ****getting runtime error "9"
subscript out of range error here.
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value

'clear the data
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
Me.txtAddress.SetFocus
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""

End Sub

Frustrated with learning add command code...
 

On Wed, 30 Jul 2008 12:56:17 -0700, Stacy
wrote:


I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.


Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") ****getting runtime error "9"
subscript out of range error here.

If you just use Worksheets, the program will assume youi mean a worksheet in
the active workbook. Whichever workbook you have active does not have a
sheet called PersonalInfoUpdate in it. If the userform is in the same
workbook as the data, use

ThisWorkbook.Workseets(...

otherwise, name the workbook specifically

Workbooks("MyBook.xls").Worksheets(...

ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value



I don't see the checkbox code in here.

ws.Cells(lRow,10).Value = IIf(Me.cbxSafety.Value, "x","")
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Frustrated with learning add command code...
 

The problem is that there is nothing specifically wrong with the code you
have... So long as you have a sheet called "PersonalInfoUpdate" then the code
will work...

Try this code on a new user form. Create a blank form. Add a text box and a
chech box and a command button. Add the following code to the command button
and run the code...

Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

rng.Value = TextBox1.Value
If CheckBox1.Value = True Then rng.Offset(0, 1).Value = "x"

End Sub
--
HTH...

Jim Thomlinson


"Stacy" wrote:

Susan,

I am still getting the runtime error and I triple checked everthing. Also,
still can't find anything about the check box commands.

Thanks!

Stacy

"Susan" wrote:

stacy - you did a lot all on your own!!!!
try adding this:

Dim wb as Workbook

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("PersonalInfoUpdate")

since you're using a userform, you have to specify the workbook and
all.
hope that helps some!
:)
susan




On Jul 30, 3:56 pm, Stacy wrote:
I have completed a userform with the following fields:

FirstName LastName Phone Email Address City State Zip

I would like to have a command button that adds these to my worksheet.

I would also like to add check boxes so they can select any initerests they
may have like transportaion, schools, safety. These can show up as an x in
the cell if selected and blank if not.

I have read and reread posts, help, goolgled them all and am still having
trouble learning how to do this. Can anybody help me with the code, or
point me to example add command codes?

I have pasted the only thing I can find with my changes below, but it does
not work and I don't know what I am doing it is just a mess.

Thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PersonalInfoUpdate") ****getting runtime error "9"
subscript out of range error here.
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtFirstName.Value
ws.Cells(iRow, 2).Value = Me.txtLastName.Value
ws.Cells(iRow, 3).Value = Me.txtPhone.Value
ws.Cells(iRow, 4).Value = Me.txtEmail.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtCity.Value
ws.Cells(iRow, 7).Value = Me.txtState.Value
ws.Cells(iRow, 8).Value = Me.txtZip.Value

'clear the data
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
Me.txtAddress.SetFocus
Me.txtCity.Value = ""
Me.txtState.Value = ""
Me.txtZip.Value = ""

End Sub