Data Entry Form: Complex Example

[Qull666]

Dear Dave Peterson and anyone who can help me,

I have been using the data entry form you have created for a long time and
you have no idea how much it has helped and worked for me. It is easy to use,
understand and effective.

The requirement I guess, grew and I was hoping for help to do a data entry
form for, say an Invoice or Journal (debit & credit). This means that with
one hit of the button, 2 or more entries will be saved into the database.
..

I will use an Invoice as an example.


Scenario:

What I want to do is to create an invoice that has fixed and multiple
entries in 1 transation.

The problem I am facing with the current VBA is that it saves one entry at a
time.

I am using the data entry form and repeating the Sub-Procedure 20 times for
one invoice if there are 20 items.

In short, D5 to D8 doesn't clear until I have finished entering the 20 items
(I changed the ClearContent D9 until D12).


Extracted from the VBA.
myCopy = "D5,D6,D7,D8,D9,D10,D11,D12"

Set inputWks = Worksheets("A-1")
Set historyWks = Worksheets("Dbase")

---------------------------------------------------------------------

Example of an Invoice.

--------------------------Sheet1 A-1----------------


----D5 = A123 (Invoice No.)
----D6 = 12-Jun-09 (Invoice Date.)
----D7 = G13 (Customer Code)
----D8 = John (Customer Name)



------D-----E--------F--G

10__ X1__Socks__5__$1
11__ X3__Shirt___8__$2
12__ Z2__Skirt___4__$3
- - - - - - -
- - - - - - -
- - - - - - -
20__ Z2__Skirt___8__$6


*D9 = Parts No.
*E9 = Parts Description
*F9 = Quantity
*G9 = Price


***Hoping to achieve this by saving only 1 time from A-1:

-------------------------Sheet2 Dbase-----------------


-----------------A------B----------C------D------E--------F-----G--H-


Row 01:__A123__12-Jun-09__G13__John___X1__Socks__5__$1
Row 02:__A123__12-Jun-09__G13__John___X3__Shirt___8__$2
Row 03:__A123__12-Jun-09__G13__John___Z2__Skirt___4__$3
- - - - - - - - - - - - - -
- - - - - - - - - - - - - -
- - - - - - - - - - - - - -
Row 20:__A123__12-Jun-09__G13__John___Z2__Skirt___8__$6



Thank you very much.

The VBA:


Option Explicit

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D37,D38,D39,D40"

Set inputWks = Worksheets("A-1")
Set historyWks = Worksheets("Dbase")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Fill up the Yellow Boxes!"
Range("D3").Select
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
'.Value = Now
'.NumberFormat = "mm/dd/yyyy hh:mm:ss"
'.Cells(nextRow, "B").Value = Application.UserName
oCol = 1 ' set to 1 instead of 3

For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
End With

'clear input cells that contain constants
' With inputWks
' On Error Resume Next
' With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
' .ClearContents
' Application.GoTo .Cells(1) ', Scroll:=True
' Range("B3").Select
' End With
' On Error GoTo 0
' End With

'Selective Clear Input Celss
With inputWks
On Error Resume Next
With Range("D149").Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1)
'Range("D6").Select
'ActiveWorkbook.Save
End With
On Error GoTo 0
End With


End Sub

 

[Dave Peterson]

Since the number of entries you want could vary (2 to ???), maybe it's better to
just not clear the input range unless you say it's ok.

You could declare a variable near the top of the code (near the other Dim
statements):

Dim resp as long

.....

Then right before you clear the values:

resp = msgbox(Prompt:="wanna clear the values?",buttons:=xlyesno)
if resp = xlyes then
'Selective Clear Input Celss
With inputWks
On Error Resume Next
....

End if

You'll still have to click the button for each entry, but it should make the
data entry a little easier.

 

[Qull666]

Dear Dave,

Hey, maybe in the coming future you create one data entry form that could
save multiple entries with one click on the button. And one that is easy to
operate like this one.

The 2 to 20 line entries are entered into a prescribed area and saved one by
one. I use an Index+Match+Small+Row to line them up for saving, the list will
reduce when data is transfered to the Dbase

I have thought of creating 20 data entry vba for each line for the 20 line
item. But it will not be practical. I think.

Thank you.

Sincerely
Stanley

 

[Dave Peterson]

If you know the range you want to fill each and every time you click the button,
then it wouldn't be difficult to do.

The problem would be to determine how many rows to fill. You could ask another
question (scrollbar/combobox...) to find out, but I thought that it would be
easier to just click until you were satisfied.

 

[Qull666]

Dear Dave,

Thanks for the information and answering this post. I appreciate it.