IM A NEW USER HAVING TROUBLE WITH CODE

[rlashmore999]

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
(e-mail address removed)
than to use a newsgroup

 

[Bob Phillips]

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("products")

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)