Looping a data input form based on form option

Good Evening (or morning, depending on your geographic location).

I have only recently started with VBA, and have managed to piece together some code that will allow me to enter some data into a worksheet. Pretty standard stuff. I include the code below for the "submit" button.

Private Sub CommandButton1_Click()
Dim kRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")

With Worksheets("Database").Range("A1: A65535")
kRow = .Find(Me.ID.Value, LookIn:=xlValues).Row
End With
ws.Cells(kRow, 10) = Me.Soldprice.Value
ws.Cells(kRow, 11) = Me.SoldDate.Value
ws.Cells(kRow, 14) = Me.PaidBy.Value
ws.Cells(kRow, 12) = Me.BuyerName.Value
ws.Cells(kRow, 13) = Me.BuyerAddress.Value
ws.Cells(kRow, 14) = Me.BuyersAddress2.Value
ws.Cells(kRow, 15) = Me.BuyersAddress3.Value
ws.Cells(kRow, 16) = Me.BuyersAddressTown.Value
ws.Cells(kRow, 17) = Me.BuyersAddressCounty.Value
ws.Cells(kRow, 18) = Me.BuyersAddressPostCode.Value
ws.Cells(kRow, 19) = Me.BuyersTelephone.Value
ws.Cells(kRow, 20) = Me.BuyersEmail.Value
ws.Cells(kRow, 21) = "SOLD"

Dim k2Row As Long
Dim a2Row As Long
Dim b2Row As Long
Dim c2Row As Long
Dim d2Row As Long
Dim e2Row As Long
Dim f2Row As Long
Dim ws2 As Worksheet
Set ws2 = Worksheets("Sales Record")

'find first empty row in database
k2Row = ws2.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws2.Cells(k2Row, 1) = Me.ID.Value
ws2.Cells(k2Row, 2) = Me.Description.Value
ws2.Cells(k2Row, 3) = Me.Soldprice.Value
ws2.Cells(k2Row, 4) = Me.SoldDate.Value
ws2.Cells(k2Row, 14) = Me.PaidBy.Value
ws2.Cells(k2Row, 5) = Me.BuyerName.Value
ws2.Cells(k2Row, 6) = Me.BuyerAddress.Value
ws2.Cells(k2Row, 7) = Me.BuyersAddress2.Value
ws2.Cells(k2Row, 8) = Me.BuyersAddress3.Value
ws2.Cells(k2Row, 9) = Me.BuyersAddressTown.Value
ws2.Cells(k2Row, 10) = Me.BuyersAddressCounty.Value
ws2.Cells(k2Row, 11) = Me.BuyersAddressPostCode.Value
ws2.Cells(k2Row, 12) = Me.BuyersTelephone.Value
ws2.Cells(k2Row, 13) = Me.BuyersEmail.Value
ws2.Cells(k2Row, 15) = "SOLD"

I am looking to add another input to my form with "quantity", where the user can optionally insert a number. The same information will then be entered except for the "ID.Value" which will increase by one for each repetition. Is anyone able to advise a way of doing this, or telling me if I have made a bodge-job of the code above?

Regards
Ralph

Hi bologne,

You could let your ID.Value = Max("A:A") +1 on the sheet itself. This way your ID would always be one greater than the biggest current number.

The next time you add a record it will be one bigger.

Hi bologne,

To give you a better answer we need a sample workbook with the userform included. You should also show where you want the quantity field on the sheet.

To attach a sample click on the "Go Advanced" below the message area and then on the Paper Clip Icon above the message area. This will allow you to attach a sample file.

Hi Ralph,

I don't understand why you can't simply type a number into the Quantity Box on the Userform.

It looks like you buy and sell individual (unique) products and you are trying to create a automated way to keep track of inventory. The whole problem is to keep ACCURATE records with a MINIMUM of work.

This means you should never type something that is already typed. You should simply be able to point and click with a mouse, if the product or person is already in your database/spreadsheet.

You have already decided on Excel (good choice) but your middle two tabs are confusing me. I believe you need 2 separate tables of your data.

1. A tab where you track all products you have for sale. The fields will be only about the items. Date of Purchase, Date of Sale, etc.
2. A second tab is where you track Customers. This would be both people you buy from and sell products to. This would include Names, addresses and such.

Now on a third tab you can have the connection between these two tables. It would have Date, Product ID, Customer ID at a minimum. This table/tab is for Transactions.

The reason you create a UserForm is so you can have a ComboDropdown box where you point and click on a Customer Name in one Field and a Product they are selling or Buying in another of the Userform fields. Then using this Userform it changes the data in the Product table of the item to Sold, with the sell date, and Price of sale.

Now the Invoice Printer needs a dropdown (or two). It should find the buyer/seller information from the Customer table and fill it in automatically. It should then allow you to point and click to what product they are dealing with and fill in that info automatically.

On the Invoice form I envision a single Validation Dropdown box for Customer ID and it would fill in all the address and phone information using a VLookup formula.
On the Invoice form also I see a Validation Dropdown for each item number and ti will fill in the Description and price using VLookup into the Product table.

I believe that creating the structure of you data comes before creating the VBA userform. I believe you need to study and learn.
1. Userform ComboBoxes see http://www.contextures.com/xlUserForm02.html
2. Look at Office Templates to see how others have solved this problem http://office.microsoft.com/en-us/te...010117255.aspx

I hope this will lead you in a better direction for getting a handle on invoices for your business.

Hi Ralph,

It looked like you are going well with VBA but you were doing it without a good plan in place. Also, after looking at a website, I believe you can download all the inventory and not need to type anything for existing stuff. I don't know if you have a customer list that could be imported but that would be the next step. Then producing invoices with only a few clicks would be my goal.

Hi,

I added a variable to CommandButton2_Click() code

Dim LastPartNo As Double

and then fixed these lines

...
...
'copy the data to the database
With ws
    LastPartNo = Application.WorksheetFunction.Max(.Range("A:A"))
  .Cells(lRow, 1).Value = LastPartNo + 1
  .Cells(lRow, 2).Value = Me.StockName.Value
...
...

This should give you the idea of how to increment the Client ID or Stock number.

Hi,

Don't you want the quantity as a number to simply put on you spreadsheet? If a customer buy 3 of them you would put in a 3 in the quanitity field. If you loop the quantity it will show three lines of 1. I don't think that is what your want. Also, you need to be more specific on which code you want "Looped".

When you ask questions, assume we know nothing and be more specific about the problem and what you expect as an answer. "Loop the Quantity" is a little to vague to act upon.

Hi,

See if this loops based on Quantity sold.

Apologies for the double-post again.

Your code works perfectly, and I have implemented it as I had intended, thank you again!

There is just one small bug in the code in that it adds a blank row between each data entry.

Once again I have attached the document in it's most recent form.

I also copy the relevant code here:

'copy the data to the database
For QuantNo = 1 To Me.sQuantity
With ws
LastPartNo = Application.WorksheetFunction.Max(.Range("A:A"))
End With

With ws

  .Cells(lRow + QuantNo, 1).Value = LastPartNo + 1
  .Cells(lRow + QuantNo, 2).Value = Me.StockName.Value
  .Cells(lRow + QuantNo, 3).Value = Me.sDescription.Value
  .Cells(lRow + QuantNo, 4).Value = Me.sDimensions.Value
  .Cells(lRow + QuantNo, 5).Value = Me.sPrice.Value
  .Cells(lRow + QuantNo, 6).Value = Me.MinimumPrice.Value

End With

With ws2
    Dim sType As Variant
    
    If Me.sCost.Value > "500" And Me.SCheck.Value = "False" Then
        sType = "M"
    ElseIf Me.SCheck.Value = "True" Then
        sType = "S"
    Else
        sType = "G"
    End If
    
    LastPurchaseCode = Application.WorksheetFunction.Max(.Range("I:I"))
      .Cells(lRow + QuantNo, 1).Value = LastPartNo + 1
      .Cells(lRow + QuantNo, 2).Value = Me.pID.Value
      .Cells(lRow + QuantNo, 3).Value = Me.sCost.Value
      .Cells(lRow + QuantNo, 4).Value = Me.PurchaseDate.Value
      .Cells(lRow + QuantNo, 5).Value = Me.pCurrency.Value
      .Cells(lRow + QuantNo, 6).Value = Me.sExchange.Value
      .Cells(lRow + QuantNo, 7).Value = Me.PaidBy.Value
      .Cells(lRow + QuantNo, 8).Value = sType
      .Cells(lRow + QuantNo, 9).Value = LastPurchaseCode + 1
    
End With
Next QuantNo

Thank you

Edit: I fixed this by changing the offset value of lRow, which I should have considered before.