Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
So frustrated! Please help! | Excel Discussion (Misc queries) | |||
Frustrated | New Users to Excel | |||
command code ( GOTO command) in formula | New Users to Excel | |||
Frustrated Professor | Excel Worksheet Functions | |||
Shell Command : Extremely Frustrated | Excel Programming |