XL03: Macro VB code for User input box and Invoice Quantity calculation

[TechStudent313]

Hello i have just joined up after browsing through the many helpful theads on the Excel forum for different parts of spreadsheet work and i have such 2 queries regarding an Invoice spreadsheet system i am working on in MS Excel 2003. The system itself includes an Invoice sheet, customer and Products datasheets which i am linking the information using a Vlookup formula that includes error trapping on the Invoice sheet.

Macro code for User input box problem

Currently i have written out a piece of VB code for a Macro on the customer datasheet that i am aiming to tether to a command button to prompt the user via Input boxes to enter in the information for 7/8 collumns on the table (will do the same for the products table once the problem has been passed).

My problem is that after using the following VB code, the input boxes appear as required and allow me to type information in but it also prompts me to enter information for the first Collumn which has a unique 3 numbered ID in which is the collumn that i dont want to be entering data into, want it to skip and only query the user from collumn B onwards:

ActiveCell = Application.InputBox("Enter the customer's name: ", _
"Name", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's address: ", _
"Address", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's town: ", _
"Town", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's county: ", _
"County", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's postcode: ", _
"Postcode", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's phone: ", _
"Phone_No.", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's fax: ", _
"Fax_No.", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

At first i thought i would only need to adjust the Offset or range numbers and it does adjust where the information the user types into the input boxes appears but only after asking for information to add to collumn A which is what i am trying to avoid if possible (Aiming to have some sort of Autonumber function in this collumn which automatically counts up when a row is added or down when deleted).

Invoice Quantity calculation problem

My other problem regards the Invoice page itself which has 8 different Collumns (Pip COde, Description, Quantity, Price per single, VAT Rate, Net Value, VAT amount and Vat Code) all collumns use the Error trapping + vlookup functions that i mentioned earlier apart from the quantity and Net Value. I am trying to somehow multiply the Net value which at the current moment has the function =IF(I18=1,E18*0.05,IF(I18=2,E18*0.175,E18)) to work out the price ex VAT by whatever number the user adds to the Quantity collumn. (Quantity collumn currently has no functions added to it). Normally i would go with the G17*D17 but when entering it since it is already working out one calculation i get the Circular reference error. An idea i did think of was just add another collumn and use the simple code with that however, but i would prefer if i could have it within the same collumn as Net Value to save space.

Thanks for your time.

Any ideas?

 

[AlphaFrog]

Macro code for User input box problem

    Dim arrPrompts As Variant, i As Integer, sReply As String
    
    arrPrompts = Array("Name", "Address", "Town", "Country", "Postcode", "Phone", "Fax")
    
    For i = 0 To UBound(arrPrompts)
        sReply = Application.InputBox("Enter the customer's " & LCase(arrPrompts(i)) & ":", arrPrompts(i), Type:=2)
        If sReply <> "False" Then Range("B1").Offset(0, i) = sReply
    Next i

Invoice Quantity calculation problem
=IF(I18=1,E18*0.05,IF(I18=2,E18*0.175,E18))*D18