Home |
Search |
Today's Posts |
#1
|
|||
|
|||
forms
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 |
#2
|
|||
|
|||
forms
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 "Monty" wrote: 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 |
#3
|
|||
|
|||
forms
Your code plops the entries from the userform into columns A:D.
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). Monty wrote: 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 "Monty" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using forms in a shared workbook | Excel Worksheet Functions | |||
Print out certain forms automatically | Excel Discussion (Misc queries) | |||
Forms that are modal in 97 are not modal in 2003 | Excel Discussion (Misc queries) | |||
how do I combine two forms with different size columns to make on. | Excel Discussion (Misc queries) | |||
Forms Toolbar vs. Control Toolbox vs. Data Validation for drop dow | Excel Discussion (Misc queries) |