Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IM A NEW USER HAVING TROUBLE WITH CODE
Im a new user and am having trouble with code to go to the next row to
enter data into a spreadsheet. This is the code I've tried (I already have items in rows 1-4 and I just want to add onto the next one) Public Sub Tester() Dim Fiddling As Workbook Dim shtProductAdd As Worksheet Dim iRow As Long Dim iCol As Long Set Fiddling = Workbooks("Fiddling.xls") Set shtProductAdd = Fiddling.Worksheets("products") With shtProductAdd iRow = .Cells(.Rows.Count, "A").End(xlUp). _ Offset(1, 0).Row iCol = .Cells(1, .Columns.Count).End(xlToLeft). _ Offset(0, 1).Column End With MsgBox Prompt:="Next row = " & iRow _ & vbNewLine _ & "Next column = " & iCol End Sub Private Sub NameButton_Click() Dim strNameButton As String Dim curBoxPrice As Currency Dim intUnitNumber As Integer Dim curSalePrice As Currency Set shtProductAdd = Application.Workbooks("Fiddling.xls").Worksheets(" products") strNameButton = InputBox("Please Enter Product Name") shtProductAdd.Range("A5") = strNameButton curBoxPrice = InputBox("Please Enter Box Price") shtProductAdd.Range("B5") = curBoxPrice intUnitNumber = InputBox("UNITS") shtProductAdd.Range("C5") = intUnitNumber curSalePrice = InputBox("Enter Sale Price") shtProductAdd.Range("D5") = curSalePrice End Sub Private Sub ExitButon_Click() Unload ProductForm End Sub Which returns me to only ever updating one single row (the 5 row obviously) The same as before I tried my latest bit of code to update the row and move down None of the code forms Ive tried from any text book has ever worked. I know why I only do row 5 but Ive tried it with ShtProductAdd (A:A") You know what happened Ive tried it with no data on the worksheet and ShtProductAdd("A") Which returned Method range of object worksheet failed NEXT To get a button called ClientButton2 on a userform on sheet 1 To open sheet 2 and go to a command button on the sheet which Opens a new userform on sheet 2 I've tried Private Sub ClientButton2_Click() 'this is on sheet1 Dim MenuButton As CommandButton Me.Hide MenuButton.Show 'command button on sheet2 End Sub Which gives me Object variable or With block variable not set Ive also got a listbox on the userform on sheet2 with this code Private Sub ClientForm_Initialize() 'the userform on sheet2 Dim ListBox1 As ListBox 'select ............default list box IstId.ListIndex = 0 'select default option button 'optTotal.Value = True ListBox1.AddItem "M&M's plain (500g)" ListBox1.AddItem "M&M's peanut (500g)" ListBox1.AddItem "Cadbury Chocolate Bars (600g)" End Sub But none of my products shows in the listbox which remains empty I would appreciate any help provided I would also find it easier to go through my mail at than to use a newsgroup |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IM A NEW USER HAVING TROUBLE WITH CODE
I am not sure I am getting this, but is this what you mean
Private Sub NameButton_Click() Dim strNameButton As String Dim curBoxPrice As Currency Dim intUnitNumber As Integer Dim curSalePrice As Currency Dim shtProductAdd As Worksheet Set shtProductAdd = _ Application.Workbooks("book1").Worksheets("product s") strNameButton = InputBox("Please Enter Product Name") With shtProductAdd iRow = .Cells(.Rows.Count, "A").End(xlUp). _ Offset(1, 0).Row iCol = .Cells(1, .Columns.Count).End(xlToLeft). _ Offset(0, 1).Column End With shtProductAdd.Cells(iRow, 1) = strNameButton curBoxPrice = InputBox("Please Enter Box Price") shtProductAdd.Cells(iRow, 2) = curBoxPrice intUnitNumber = InputBox("UNITS") shtProductAdd.Cells(iRow, 3) = intUnitNumber curSalePrice = InputBox("Enter Sale Price") shtProductAdd.Cells(iRow, 4) = curSalePrice End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... Im a new user and am having trouble with code to go to the next row to enter data into a spreadsheet. This is the code I've tried (I already have items in rows 1-4 and I just want to add onto the next one) Public Sub Tester() Dim Fiddling As Workbook Dim shtProductAdd As Worksheet Dim iRow As Long Dim iCol As Long Set Fiddling = Workbooks("Fiddling.xls") Set shtProductAdd = Fiddling.Worksheets("products") With shtProductAdd iRow = .Cells(.Rows.Count, "A").End(xlUp). _ Offset(1, 0).Row iCol = .Cells(1, .Columns.Count).End(xlToLeft). _ Offset(0, 1).Column End With MsgBox Prompt:="Next row = " & iRow _ & vbNewLine _ & "Next column = " & iCol End Sub Private Sub NameButton_Click() Dim strNameButton As String Dim curBoxPrice As Currency Dim intUnitNumber As Integer Dim curSalePrice As Currency Set shtProductAdd = Application.Workbooks("Fiddling.xls").Worksheets(" products") strNameButton = InputBox("Please Enter Product Name") shtProductAdd.Range("A5") = strNameButton curBoxPrice = InputBox("Please Enter Box Price") shtProductAdd.Range("B5") = curBoxPrice intUnitNumber = InputBox("UNITS") shtProductAdd.Range("C5") = intUnitNumber curSalePrice = InputBox("Enter Sale Price") shtProductAdd.Range("D5") = curSalePrice End Sub Private Sub ExitButon_Click() Unload ProductForm End Sub Which returns me to only ever updating one single row (the 5 row obviously) The same as before I tried my latest bit of code to update the row and move down None of the code forms Ive tried from any text book has ever worked. I know why I only do row 5 but Ive tried it with ShtProductAdd (A:A") You know what happened Ive tried it with no data on the worksheet and ShtProductAdd("A") Which returned Method range of object worksheet failed NEXT To get a button called ClientButton2 on a userform on sheet 1 To open sheet 2 and go to a command button on the sheet which Opens a new userform on sheet 2 I've tried Private Sub ClientButton2_Click() 'this is on sheet1 Dim MenuButton As CommandButton Me.Hide MenuButton.Show 'command button on sheet2 End Sub Which gives me Object variable or With block variable not set Ive also got a listbox on the userform on sheet2 with this code Private Sub ClientForm_Initialize() 'the userform on sheet2 Dim ListBox1 As ListBox 'select ............default list box IstId.ListIndex = 0 'select default option button 'optTotal.Value = True ListBox1.AddItem "M&M's plain (500g)" ListBox1.AddItem "M&M's peanut (500g)" ListBox1.AddItem "Cadbury Chocolate Bars (600g)" End Sub But none of my products shows in the listbox which remains empty I would appreciate any help provided I would also find it easier to go through my mail at than to use a newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003: Having trouble with conditional formatting with custom user function (Repost) | Excel Worksheet Functions | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
Trouble with editing data from a user form | Excel Programming | |||
Textbox trouble on a user form | Excel Programming | |||
User form trouble | Excel Programming |