forms

[Guest]

i have the following working within a macro:.

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

Is there any way a can change this to do the following:- when i open the
form it moves to the next column, for example if i allocate cell AF 21 then
the next time i open the form it would go to cell AG21

Thanks

Monty

 

[Guest]

Sorry the full macro is this

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

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

 

[Dave Peterson]

Your code plops the entries from the userform into columns A.

This seems like a pretty usual layout for data like this.

But you could use the same kind of technique to find the next column:

Dim NextCol as long

with ws
nextcol = .cells(1,.columns.count).end(xltoleft).offset(0,1).column
end with

But this finds the next available column in row 1.

I'm not sure how your code should know to look at row 21 (or any other row).